package gov.nhso.prov.acc.apps.acc67;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import gov.nhso.prov.acc.apps.acc6.ACC60108;
import gov.nhso.prov.acc.beans.Acc67ExpenseCategoryBean;
import gov.nhso.prov.acc.beans.Acc67SuspendBean;
import gov.nhso.prov.acc.beans.Acc6AllocateBean;
import gov.nhso.prov.acc.beans.Acc6ChequeBean;
import gov.nhso.prov.acc.beans.Acc6ChequeDetailBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseChequeBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseTransferBean;
import gov.nhso.prov.acc.beans.Acc6VenderBean;
import gov.nhso.prov.acc.beans.Acc7ExpenseBean;
import gov.nhso.prov.acc.beans.Acc7ExpenseDetailBean;
import gov.nhso.prov.acc.beans.Acc7TransactionBean;
import gov.nhso.prov.acc.beans.ProvinceBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.UserInfo;
import gov.nhso.prov.acc.utils.Utility;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;

import javazoom.upload.MultipartFormDataRequest;
import javazoom.upload.UploadFile;

@SuppressWarnings({"rawtypes","unchecked"})
public class AccUpload extends Utility {
	public String url = "";
	public boolean flag = true;
	
	public AccUpload(HttpServletRequest request, MultipartFormDataRequest mrequest, HttpServletResponse response) {
		this.message(request).clearMsg();
		String app = mrequest.getParameter("app");
		String job = mrequest.getParameter("job");
		if (app.equals("ACC60101") && job.equals("upload")){
			this.upload(request,mrequest);
		} else if (app.equals("ACC60301") && job.equals("upload")){
			this.ACC60301upload(request,mrequest);
		} else if (app.equals("ACC60301") && job.equals("confirmUpload")){
			this.ACC60301confirmUpload(request,mrequest);
		} else if (app.equals("ACC60302") && job.equals("upload")){
			this.ACC60302upload(request,mrequest);
		} else if (app.equals("ACC60302") && job.equals("confirmUpload")){
			this.ACC60302confirmUpload(request,mrequest);
		}  else if (app.equals("ACC60107") && job.equals("upload")){
			this.ACC60107Upload(request,mrequest);
		} else if (app.equals("ACC60310")){
			this.ACC60310upload(request, mrequest, response);
		} else if (app.equals("ACC70302")){
			this.ACC70302upload(request, mrequest, response);
		}   else if (app.equals("ACC60108")){
			this.acc60108Upload(request, mrequest, response);
		} 
	}
	
	private String upload(HttpServletRequest request,MultipartFormDataRequest mrequest){
		Hashtable files = mrequest.getFiles();
		UploadFile file = (UploadFile)files.get("xlsFile");
		Connection conn = null;
		ISUD dao = null;
		try {
			UserInfo userInfo = this.user(request);
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc6AllocateBean allocateBean = new Acc6AllocateBean();
			
			String sql = "";
			String table = this.schema + ".ACC6_ALLOCATE";
			String[] fileName = null;
			String budgetYear = mrequest.getParameter("budgetYear");
			Map<String, String> map = null;
			
			fileName = file.getFileName().split("\\.");
			if(fileName[1].equals("xls")){
				POIFSFileSystem fsFileSystem = new POIFSFileSystem(file.getInpuStream());
				HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
				HSSFSheet hssfSheet = workBook.getSheetAt(0);
				int rowNum = hssfSheet.getLastRowNum();
				if(rowNum > 0){
					
					sql = "SELECT ALLOCATE_CODE FROM "+table+" WHERE ALLOCATE_CODE = "+budgetYear;
					map =  dao.findDataSql(sql);
					
					if(map.get("ALLOCATE_CODE") == "" || map.get("ALLOCATE_CODE") == null){
						allocateBean.setBudgetYear(budgetYear);
						allocateBean.setAllocateCode(budgetYear);
						allocateBean.setSapCode(budgetYear);
						allocateBean.setAllocateName("รหัสแจ้งจัดสรรเงินให้คู่สัญญา "+budgetYear.substring(2));
						allocateBean.setAllocateLevel("0");
						allocateBean.setAllocateSeq("0");
						allocateBean.setParentCode("0");
						dao.insert(allocateBean);
					}
					
					for(int i=0;i<rowNum;i++){
						HSSFRow row = hssfSheet.getRow(i);
						HSSFCell cellA1 = row.getCell(0);
						HSSFCell cellA2 = row.getCell(1);
						HSSFCell cellA3 = row.getCell(2);
						cellA1.setCellType(Cell.CELL_TYPE_STRING);
						cellA2.setCellType(Cell.CELL_TYPE_STRING);
						cellA3.setCellType(Cell.CELL_TYPE_STRING);
						
						allocateBean = new Acc6AllocateBean();
						allocateBean.setBudgetYear(budgetYear);
						allocateBean.setAllocateCode(cellA1.toString());
						allocateBean.setSapCode(cellA2.toString());
						allocateBean.setAllocateName(cellA3.toString());
						allocateBean.setCreatedBy(userInfo.getUserId());
						allocateBean.setUpdatedBy(userInfo.getUserId());
						dao.insert(allocateBean);
						
					}
				}else{
					this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
				}
			}else{
				this.message(request).setMsg("W", "ไฟล์ excel ต้องเป็นนามสกุล xls");
			}
			request.setAttribute("budgetYear", budgetYear);
			conn.commit();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			
		} catch (IOException e) {
			e.printStackTrace();
			
		} catch (SQLException e) {
			e.printStackTrace();
			
		} catch (Exception e) {
			e.printStackTrace();
			
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
			this.url = this.manager(request);
		}
		
		return this.url;
	}
	private String manager(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String sql = "";
			List<Map<String, String>> list = null;
			String table = this.schema + ".ACC6_ALLOCATE";
			Map<String, String> map = null;
			String budgetYear = this.getAttr(request, "budgetYear");
			
			sql = "SELECT ALLOCATE_CODE,ALLOCATE_NAME AS ALLOCATE_NAME,BUDGET_YEAR,PARENT_CODE,ALLOCATE_LEVEL,ALLOCATE_SEQ,ALLOCATE_APPLY "
				+ "FROM "+table+" "
				+ "WHERE PARENT_CODE IS NOT NULL AND ALLOCATE_LEVEL IS NOT NULL AND ACTIVE = 'Y' AND BUDGET_YEAR ='"+budgetYear+"' ORDER BY ALLOCATE_CODE ASC";
			list = dao.listDataSql(sql);
			
			request.setAttribute("listData", list);
			map = list.get(0);
			sql = "SELECT ALLOCATE_CODE,SAP_CODE,ALLOCATE_NAME,BUDGET_YEAR FROM "+table+" WHERE BUDGET_YEAR = '"+budgetYear+"' "
			+ "AND ALLOCATE_CODE != "+map.get("BUDGET_YEAR").substring(2)+"000000 AND ACTIVE = 'Y' "
			+ "AND PARENT_CODE IS NULL AND ALLOCATE_LEVEL IS NULL ORDER BY ALLOCATE_CODE ASC";
			list = null;
			list = dao.listDataSql(sql);
			request.setAttribute("listDataDialog", list);
			request.setAttribute("budgetYear", budgetYear);
			
			this.url = "/apps/acc6/ACC60101C.jsp";
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String ACC60301upload(HttpServletRequest request, MultipartFormDataRequest mrequest){
		Connection conn = null;
		ISUD dao = null;
		Hashtable files = mrequest.getFiles();
		UploadFile file = (UploadFile)files.get("xlsFile");
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			List expenseCategoryList = new ArrayList();
			List allocateList = new ArrayList();
			List list = new ArrayList();
			int cv = 0;
			StringBuffer sb = new StringBuffer();
			Map<String, String> map = new HashMap<String, String>();
			double remain = 0d;
			String nagative = "";
			int j =0;
			String provinceId = mrequest.getParameter("provinceId");
			String budgetYear = mrequest.getParameter("budgetYear");
			String expenseDate = mrequest.getParameter("expenseDate");
			String expenseSubject = mrequest.getParameter("expenseSubject");
			String expenseDesc = mrequest.getParameter("expenseDesc");
			String docNo = this.genCode(dao).getExpenseDocNo(budgetYear,provinceId,"T");
			
			String[] fileName = null;
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			fileName = file.getFileName().split("\\.");
			if(fileName[1].equals("xls")){
				POIFSFileSystem fsFileSystem = new POIFSFileSystem(file.getInpuStream());
				HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
				HSSFSheet hssfSheet = workBook.getSheetAt(0);
				int rowNum = hssfSheet.getLastRowNum();
				System.out.println("rowNum >> " + rowNum);
				if(rowNum > 0){
					Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
					Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
					Acc6AllocateBean allocateBean = new Acc6AllocateBean();
					expenseCategoryBean.setCategoryAcc("6");
					expenseCategoryBean.setActive("Y");
					list = dao.selectList(expenseCategoryBean);
					for (j =0;j<list.size();j++){
						Acc67ExpenseCategoryBean bean = (Acc67ExpenseCategoryBean)list.get(j);
						expenseCategoryList.add(bean.getCategoryId());
					}
					//allocateBean.setBudgetYear(budgetYear);
					list = dao.selectList(allocateBean);
					for (j =0;j<list.size();j++){
						Acc6AllocateBean bean = (Acc6AllocateBean)list.get(j);
						allocateList.add(bean.getAllocateCode());
					}
					UserInfo userInfo = new UserInfo();
					expenseBean.setDocNo(docNo);
					expenseBean.setExpenseType("T");
					expenseBean.setBudgetYear(budgetYear);
					expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(this.encodeUTF(expenseDate)));
					expenseBean.setProvinceId(provinceId);
					expenseBean.setExpenseSubject(this.encodeUTF(expenseSubject));
					expenseBean.setExpenseDesc(this.encodeUTF(expenseDesc));
					expenseBean.setCreatedBy(userInfo.getUserId());
					expenseBean.setUpdatedBy(userInfo.getUserId());
					
					dao.insert(expenseBean);
					
					for(int i=0;i<rowNum;i++){
						expenseTransferBean = new Acc6ExpenseTransferBean();
						HSSFRow row = hssfSheet.getRow(i+1);
						HSSFCell cellA1 = row.getCell(0);
						HSSFCell cellA2 = row.getCell(1);
						HSSFCell cellA3 = row.getCell(2);
						HSSFCell cellA4 = row.getCell(4);
						HSSFCell cellA5 = row.getCell(5);
						HSSFCell cellA6 = row.getCell(7);
						HSSFCell cellA7 = row.getCell(8);
						HSSFCell cellA8 = row.getCell(9);
						if(cellA1 != null){
							cellA1.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA2 != null){
							cellA2.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA3 != null){
							cellA3.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA4 != null){
							cellA4.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA5 != null){
							cellA5.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA6 != null){
							cellA6.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA7 != null){
							cellA7.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA8 != null){
							cellA8.setCellType(Cell.CELL_TYPE_STRING);
						}
						
						expenseTransferBean.setDocNo(docNo);
						expenseTransferBean.setTransferSeq(String.valueOf(i+1));
						if (!dao.chkData(cellA1).equals("") && dao.chkData(cellA1).length() <= 50) {
							expenseTransferBean.setRefNo(String.valueOf(dao.chkData(cellA1)));
						} else if (dao.chkData(cellA1).length() > 50){
							this.message(request).setMsg("E", "เลขที่เอกสารอ้างอิงบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 50 กรุณรตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (dao.chkData(cellA1).equals("")) {
							this.message(request).setMsg("E", "เลขที่เอกสารอ้างอิงบรรทัดที่ : " + (i+2) +" ไม่พบเลขที่เอกสารอ้างอิงกรุณาตรวจสอบ ");
							this.flag = false;
							conn.rollback();
							break;
						}
						
						if (!dao.chkData(cellA2).equals("") && dao.chkData(cellA2).matches("\\d{1,8}") && allocateList.contains(dao.chkData(cellA2).trim())) {
							expenseTransferBean.setAllocateCode(String.valueOf(dao.chkData(cellA2)));
						} else if (!allocateList.contains(dao.chkData(cellA2).trim())) { 
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" รหัสนี้ไม่มีในระบบกรุณาตรวจสอบกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (!dao.chkData(cellA2).matches("\\d")) {
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (dao.chkData(cellA2).length() > 8) {
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 8 ตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (dao.chkData(cellA2).equals("")) {
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่พบรหัสแจ้งจัดสรรเงินให้คู่สัญญากรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						}
						cv = 0;
						sb.delete(0, sb.length());
						sb.append("SELECT COUNT(VENDER_CODE) AS COUNT_VENDER_CODE ");
						sb.append("FROM "+this.schema+".ACC6_VENDER ");
						sb.append("WHERE VENDER_CODE = '"+dao.chkData(cellA3)+"' AND VENDER_TYPE <> 'C' AND VENDER_ACCOUNT IS NOT NULL ");
						cv = Integer.parseInt(dao.findDataSql(sb.toString()).get("COUNT_VENDER_CODE").toString());
						if (dao.chkData(cellA3).equals("")) {
							this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่พบรหัสคู่สัญญากรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else {
							if (dao.chkData(cellA3).length() <= 10 && cv > 0) {
								expenseTransferBean.setVenderCode(String.valueOf(dao.chkData(cellA3)));
							} else if (dao.chkData(cellA3).length() > 10) {
								this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 10 กรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (cv == 0) {
								this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" คู่สัญญานี้ไม่มีในระบบกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} 
						}
						if (!dao.chkData(cellA4).equals("") && dao.chkData(cellA4).replace(",", "").matches("(\\.|\\d)+")) {
							expenseTransferBean.setTransferAmount(String.valueOf(dao.chkData(cellA4).replace(",", "")));
						} else if (!dao.chkData(cellA4).replace(",", "").matches("\\d")) {
							this.message(request).setMsg("E", "จำนวนเงินบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (dao.chkData(cellA4).equals("")){
							this.message(request).setMsg("E", "จำนวนเงินบรรทัดที่ : " + (i+2) +" ไม่มีจำนวนเงินกรุณาตรวจสอบ ");
							this.flag = false;
							conn.rollback();
							break;
						}
						
						if (!dao.chkData(cellA5).equals("")) {
							if (dao.chkData(cellA5).matches("\\d+") && expenseCategoryList.contains(dao.chkData(cellA5).trim())) {
								expenseTransferBean.setCategoryId(String.valueOf(dao.chkData(cellA5)));
							} else if (!dao.chkData(cellA5).matches("\\d")) { 
								this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (!expenseCategoryList.contains(dao.chkData(cellA5).trim())) {
								this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" ไม่มีข้อมูลในระบบกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							}
						} else {
							expenseTransferBean.setCategoryId(dao.chkData(cellA5));
						}
						
						expenseTransferBean.setResponsibleName(String.valueOf(dao.chkData(cellA6)));
						expenseTransferBean.setTransferDesc(String.valueOf(dao.chkData(cellA7)));
						if (!dao.chkData(cellA8).equals("") && (dao.chkData(cellA8).equals("Y") || dao.chkData(cellA8).equals("N"))) {
							expenseTransferBean.setIsBorrow(String.valueOf(dao.chkData(cellA8)));
						} else if (dao.chkData(cellA5).equals("")) { 
							this.message(request).setMsg("E", "สถานะเงินยืมบรรทัดที่ : " + (i+2) +" ไม่พบสถานะเงินยืม");
							this.flag = false;
							conn.rollback();
							break;
						} else if ((!dao.chkData(cellA8).equals("Y") || !dao.chkData(cellA8).equals("N"))) {
							this.message(request).setMsg("E", "สถานะเงินยืมบรรทัดที่ : " + (i+2) +" ไม่ถูกต้อง");
							this.flag = false;
							conn.rollback();
							break;
						}
						map = this.getRemain(provinceId, budgetYear, String.valueOf(dao.chkData(cellA2)));
						remain = Double.parseDouble(map.get("remain").toString());
						nagative = map.get("nagative").toString();
						allocateBean = new Acc6AllocateBean();
						allocateBean.setAllocateCode(dao.chkData(cellA2));
						allocateBean = (Acc6AllocateBean) dao.select(allocateBean);
						
						if (allocateBean.getAllocateApply().equals("Y")) {
							if (remain < Double.parseDouble(dao.chkData(cellA4).replaceAll(",", "")) && nagative.equals("0")){
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ยอดคงเหลือ "+this.digitUtil().formatDecimal(remain, 2)+" ติดลบไม่ได้ ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (remain < 0 && (nagative.equals("1") || nagative.equals("0"))){
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ยอดคงเหลือ "+this.digitUtil().formatDecimal(remain, 2)+" ติดลบได้ 1 ครั้ง");
								this.flag = false;
								conn.rollback();
								break;
							} else {
								dao.insert(expenseTransferBean);
							}
						} else {
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" อยู่ในสถานะไม่ใช้งานกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						}
					}
					ProvinceBean provinceBean = new ProvinceBean();
					List listProvice = new ArrayList();
					if(this.user(request).getAreaLevel().equals("3")){
						provinceBean.setProvinceId(this.user(request).getAreaCode());
						listProvice = dao.selectList(provinceBean," ORDER BY PROVINCE_NAME");
					}else if(this.user(request).getAreaLevel().equals("2")){
						String condition = "";
						condition += " AND PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '" + this.user(request).getAreaCode()+ "' ) ";
						listProvice = dao.selectList(provinceBean," "+ condition + " ORDER BY PROVINCE_NAME");
					}else{
						listProvice = dao.selectList(provinceBean);
					}
					
					request.setAttribute("budgetYear", budgetYear);
					request.setAttribute("provinceId", provinceId);
					request.setAttribute("listProvice", listProvice);
					request.setAttribute("docNo", docNo);
				}else if (rowNum < 0 && this.flag){
					this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
				}
			}else{
				this.message(request).setMsg("W", "ไฟล์ excel ต้องเป็นนามสกุล xls");
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				if (this.flag) {
					this.ACC60301viewEdit(request);
				} else {
					this.ACC60301add(request);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String ACC60301confirmUpload(HttpServletRequest request, MultipartFormDataRequest mrequest){
		Connection conn = null;
		ISUD dao = null;
		Hashtable files = mrequest.getFiles();
		UploadFile file = (UploadFile)files.get("xlsFile");
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			String[] fileName = null;
			List expenseCategoryList = new ArrayList();
			List allocateList = new ArrayList();
			List list = new ArrayList();
			Map<String, String> map = new HashMap<String, String>();
			int cv = 0;
			StringBuffer sb = new StringBuffer();
			double remain = 0d;
			String nagative = "";
			int j = 0;
			String docNo = mrequest.getParameter("docNo");
			String provinceId = mrequest.getParameter("provinceId");
			String budgetYear = mrequest.getParameter("budgetYear");
			List listExpenseTransfer = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			fileName = file.getFileName().split("\\.");
			docNo = this.encodeUTF(docNo);
			if(docNo.equals("สร้างจากระบบ")){
				Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
				Acc6AllocateBean allocateBean = new Acc6AllocateBean();
				expenseCategoryBean.setCategoryAcc("6");
				expenseCategoryBean.setActive("Y");
				list = dao.selectList(expenseCategoryBean);
				for (j =0;j<list.size();j++){
					Acc67ExpenseCategoryBean bean = (Acc67ExpenseCategoryBean)list.get(j);
					expenseCategoryList.add(bean.getCategoryId());
				}
				//allocateBean.setBudgetYear(budgetYear);
				list = dao.selectList(allocateBean);
				for (j =0;j<list.size();j++){
					Acc6AllocateBean bean = (Acc6AllocateBean)list.get(j);
					allocateList.add(bean.getAllocateCode());
				}
				docNo = this.genCode(dao).getExpenseDocNo(budgetYear,provinceId,"T");
				String expenseDate = mrequest.getParameter("expenseDate");
				String expenseSubject = mrequest.getParameter("expenseSubject");
				String expenseDesc = mrequest.getParameter("expenseDesc");
				
				if(fileName[1].equals("xls")){
					POIFSFileSystem fsFileSystem = new POIFSFileSystem(file.getInpuStream());
					HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
					HSSFSheet hssfSheet = workBook.getSheetAt(0);
					int rowNum = hssfSheet.getLastRowNum();
					if(rowNum > 0){
						expenseDate = this.encodeUTF(expenseDate);
						expenseSubject = this.encodeUTF(expenseSubject);
						expenseDesc = this.encodeUTF(expenseDesc);
						
						expenseBean = new Acc6ExpenseBean();
						expenseBean.setDocNo(docNo);
						expenseBean.setExpenseStatus("A");
						expenseBean.setExpenseType("T");
						expenseBean.setBudgetYear(budgetYear);
						expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
						expenseBean.setProvinceId(provinceId);
						expenseBean.setExpenseSubject(expenseSubject);
						expenseBean.setExpenseDesc(expenseDesc);
						expenseBean.setCreatedBy(this.user(request).getUserId());
						expenseBean.setUpdatedBy(this.user(request).getUserId());
						dao.insert(expenseBean);
						
						for(int i=0;i<rowNum;i++){
							expenseTransferBean = new Acc6ExpenseTransferBean();
							HSSFRow row = hssfSheet.getRow(i+1);
							HSSFCell cellA1 = row.getCell(0);
							HSSFCell cellA2 = row.getCell(1);
							HSSFCell cellA3 = row.getCell(2);
							HSSFCell cellA4 = row.getCell(4);
							HSSFCell cellA5 = row.getCell(5);
							HSSFCell cellA6 = row.getCell(7);
							HSSFCell cellA7 = row.getCell(8);
							HSSFCell cellA8 = row.getCell(9);
							
							if(cellA1 != null){
								cellA1.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA2 != null){
								cellA2.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA3 != null){
								cellA3.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA4 != null){
								cellA4.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA5 != null){
								cellA5.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA6 != null){
								cellA6.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA7 != null){
								cellA7.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA8 != null){
								cellA8.setCellType(Cell.CELL_TYPE_STRING);
							}
							
							expenseTransferBean.setDocNo(docNo);
							expenseTransferBean.setTransferSeq(String.valueOf(i+1));
							if (!dao.chkData(cellA1).equals("") && dao.chkData(cellA1).length() <= 50) {
								expenseTransferBean.setRefNo(String.valueOf(dao.chkData(cellA1)));
							} else if (dao.chkData(cellA1).length() > 50){
								this.message(request).setMsg("E", "เลขที่เอกสารอ้างอิงบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 50 กรุณรตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (dao.chkData(cellA1).equals("")) {
								this.message(request).setMsg("E", "เลขที่เอกสารอ้างอิงบรรทัดที่ : " + (i+2) +" ไม่พบเลขที่เอกสารอ้างอิงกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							}
							
							if (!dao.chkData(cellA2).equals("") && dao.chkData(cellA2).matches("\\d{1,8}") && allocateList.contains(dao.chkData(cellA2).trim())) {
								expenseTransferBean.setAllocateCode(String.valueOf(dao.chkData(cellA2)));
							} else if (!allocateList.contains(dao.chkData(cellA2).trim())) { 
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" รหัสนี้ไม่มีในระบบกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (!dao.chkData(cellA2).matches("\\d")) {
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (dao.chkData(cellA2).length() > 8) {
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 8 ตัวอักษรกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (dao.chkData(cellA2).equals("")) {
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่พบรหัสแจ้งจัดสรรเงินให้คู่สัญญากรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							}
							cv = 0;
							sb.delete(0, sb.length());
							sb.append("SELECT COUNT(VENDER_CODE) AS COUNT_VENDER_CODE ");
							sb.append("FROM "+this.schema+".ACC6_VENDER ");
							sb.append("WHERE VENDER_CODE = '"+dao.chkData(cellA3)+"' AND VENDER_TYPE <> 'C' AND VENDER_ACCOUNT IS NOT NULL ");
							cv = Integer.parseInt(dao.findDataSql(sb.toString()).get("COUNT_VENDER_CODE").toString());
							if (dao.chkData(cellA3).equals("")) {
								this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่พบรหัสคู่สัญญากรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else {
								if (dao.chkData(cellA3).length() <= 10 && cv > 0) {
									expenseTransferBean.setVenderCode(String.valueOf(dao.chkData(cellA3)));
								} else if (dao.chkData(cellA3).length() > 10) {
									this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 10 กรุณาตรวจสอบ");
									this.flag = false;
									conn.rollback();
									break;
								} else if (cv == 0) {
									this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" คู่สัญญานี้ไม่มีในระบบกรุณาตรวจสอบ");
									this.flag = false;
									conn.rollback();
									break;
								} 
							}
							if (!dao.chkData(cellA4).equals("") && dao.chkData(cellA4).replace(",", "").matches("(\\.|\\d)+")) {
								expenseTransferBean.setTransferAmount(String.valueOf(dao.chkData(cellA4).replace(",", "")));
							} else if (!dao.chkData(cellA4).replace(",", "").matches("\\d")) {
								this.message(request).setMsg("E", "จำนวนเงินบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (dao.chkData(cellA4).equals("")){
								this.message(request).setMsg("E", "จำนวนเงินบรรทัดที่ : " + (i+2) +" ไม่มีจำนวนเงินกรุณาตรวจสอบ ");
								this.flag = false;
								conn.rollback();
								break;
							}
							
							if (!dao.chkData(cellA5).equals("")) {
								if (dao.chkData(cellA5).matches("\\d+") && expenseCategoryList.contains(dao.chkData(cellA5).trim())) {
									expenseTransferBean.setCategoryId(String.valueOf(dao.chkData(cellA5)));
								} else if (!dao.chkData(cellA5).matches("\\d")) { 
									this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
									this.flag = false;
									conn.rollback();
									break;
								} else if (!expenseCategoryList.contains(dao.chkData(cellA5).trim())) {
									this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" ไม่มีข้อมูลในระบบกรุณาตรวจสอบ");
									this.flag = false;
									conn.rollback();
									break;
								}
							} else {
								expenseTransferBean.setCategoryId(dao.chkData(cellA5));
							}
							
							expenseTransferBean.setResponsibleName(String.valueOf(dao.chkData(cellA6)));
							expenseTransferBean.setTransferDesc(String.valueOf(dao.chkData(cellA7)));
							if (!dao.chkData(cellA8).equals("") && (dao.chkData(cellA8).equals("Y") || dao.chkData(cellA8).equals("N"))) {
								expenseTransferBean.setIsBorrow(String.valueOf(dao.chkData(cellA8)));
							} else if (dao.chkData(cellA5).equals("")) { 
								this.message(request).setMsg("E", "สถานะเงินยืมบรรทัดที่ : " + (i+2) +" ไม่พบสถานะเงินยืม");
								this.flag = false;
								conn.rollback();
								break;
							} else if ((!dao.chkData(cellA8).equals("Y") || !dao.chkData(cellA8).equals("N"))) {
								this.message(request).setMsg("E", "สถานะเงินยืมบรรทัดที่ : " + (i+2) +" ไม่ถูกต้อง");
								this.flag = false;
								conn.rollback();
								break;
							}
							map = this.getRemain(provinceId, budgetYear, String.valueOf(dao.chkData(cellA2)));
							remain = Double.parseDouble(map.get("remain").toString());
							nagative = map.get("nagative").toString();
							allocateBean = new Acc6AllocateBean();
							allocateBean.setAllocateCode(dao.chkData(cellA2));
							allocateBean = (Acc6AllocateBean) dao.select(allocateBean);
							
							if (allocateBean.getAllocateApply().equals("Y")) {
								if (remain < Double.parseDouble(dao.chkData(cellA4).replaceAll(",", "")) && nagative.equals("0")){
									this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ยอดคงเหลือ "+this.digitUtil().formatDecimal(remain, 2)+" ติดลบไม่ได้ ");
									this.flag = false;
									conn.rollback();
									break;
								} else if (remain < 0 && (nagative.equals("1") || nagative.equals("0"))){
									this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ยอดคงเหลือ "+this.digitUtil().formatDecimal(remain, 2)+" ติดลบได้ 1 ครั้ง");
									this.flag = false;
									conn.rollback();
									break;
								} else {
									dao.insert(expenseTransferBean);
								}
							} else {
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" อยู่ในสถานะไม่ใช้งานกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							}
						}
						expenseBean = new Acc6ExpenseBean();
						expenseBean.setDocNo(docNo);
						expenseBean.setProvinceId(provinceId);
						expenseBean.setBudgetYear(budgetYear);
						expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
						
						Map pv = new HashMap();
						expenseTransferBean = new Acc6ExpenseTransferBean();
						expenseTransferBean.setDocNo(docNo);
						expenseTransferBean.setActive("Y");
						listExpenseTransfer = dao.selectList(expenseTransferBean," ORDER BY TRANSFER_SEQ ASC");
						for (int i = 0; i < listExpenseTransfer.size(); i++) {
							Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpenseTransfer.get(i);
							sb.delete(0, sb.length());
							sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
							sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
							sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+bean.getAllocateCode()+"'");
							pv = dao.findDataSql(sb.toString());

							if (pv.get("ALLOCATE_NAME") == null){
								bean.setAllocateName("");
							}else{
								bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
							}
							if (pv.get("ALLOCATE_NAME_SUB") == null){
								bean.setAllocateSub("");
							}else{
								bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
							}
							sb.delete(0, sb.length());
							pv = new HashMap();
							sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
							sb.append("FROM "+this.schema+".ACC6_VENDER ");
							sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
							pv = dao.findDataSql(sb.toString());
							
							if (pv.get("VENDER_NAME") == null){
								bean.setVenderName("");
							}else{
								bean.setVenderName(pv.get("VENDER_NAME").toString());
							}
							if (pv.get("VENDER_NAME_SUB") == null){
								bean.setVenderSub("");
							}else{
								bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
							}
						}
						request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
						request.setAttribute("docNo", docNo);
						request.setAttribute("provinceId", provinceId);
						request.setAttribute("budgetYear", budgetYear);
						request.setAttribute("expenseBean", expenseBean);
						request.setAttribute("listExpenseTransfer", listExpenseTransfer);
						
					}else if (rowNum < 0 && this.flag){
						this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
					}
				}else{
					this.message(request).setMsg("W", "ไฟล์ excel ต้องเป็นนามสกุล xls");
				}
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				if (this.flag) {
					this.ACC60301view(request);
				} else {
					this.ACC60301add(request);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String ACC60301viewEdit(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			List listCategory = new ArrayList();
			List listExpenseTransfer = new ArrayList();
			List listVender = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			Acc6VenderBean venderBean = new Acc6VenderBean(); 

			if (!docNo.equals("") && !budgetYear.equals("") && !provinceId.equals("")){
				expenseCategoryBean.setCategoryAcc("6");
				expenseCategoryBean.setActive("Y");
				listCategory = dao.selectList(expenseCategoryBean);

				expenseBean.setDocNo(docNo);
				expenseBean.setBudgetYear(budgetYear);
				expenseBean.setProvinceId(provinceId);
				expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
						
				String sql = "";
				StringBuffer sb = new StringBuffer();
				Map pv = new HashMap();
				expenseTransferBean.setDocNo(docNo);
				listExpenseTransfer = dao.selectList(expenseTransferBean," ORDER BY TRANSFER_SEQ ASC");
				for (int i = 0; i < listExpenseTransfer.size(); i++) {
					Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpenseTransfer.get(i);
					String allocateCode = bean.getAllocateCode();
					String venderCode = bean.getVenderCode();
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
					sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
					sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+allocateCode+"'");
					pv = dao.findDataSql(sb.toString());
					
					if (pv.get("ALLOCATE_NAME") == null){
						bean.setAllocateName("");
					}else{
						bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
					}
					if (pv.get("ALLOCATE_NAME_SUB") == null){
						bean.setAllocateSub("");
					}else{
						bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
					}
					sb.delete(0, sb.length());
					pv = new HashMap();
					sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
					sb.append("FROM "+this.schema+".ACC6_VENDER ");
					sb.append("WHERE VENDER_CODE='"+venderCode+"'");
					pv = dao.findDataSql(sb.toString());
					
					if (pv.get("VENDER_NAME") == null){
						bean.setVenderName("");
					}else{
						bean.setVenderName(pv.get("VENDER_NAME").toString());
					}
					if (pv.get("VENDER_NAME_SUB") == null){
						bean.setVenderSub("");
					}else{
						bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
					}
					
				}
					
				sql = "SELECT PROVINCE_ID, PROVINCE_NAME FROM " + this.schema + ".PROVINCE WHERE PROVINCE_ID != '"+this.user(request).getAreaCode()+"' AND NHSO_ZONE NOT IN ('14', '15') ORDER BY PROVINCE_NAME ASC";
				request.setAttribute("listAllProvice", dao.listDataSql(sql));
					
				String maxSeq = dao.chkData(dao.findDataSql("SELECT MAX(TRANSFER_SEQ) AS MAX_TRANSFER_SEQ FROM " + this.schema + ".ACC6_EXPENSE_TRANSFER WHERE DOC_No = '"+docNo+"'").get("MAX_TRANSFER_SEQ"));
				venderBean.setProvinceId(provinceId);
				listVender = dao.selectList(venderBean);
					
				request.setAttribute("lsAllocate", this.dataUtil(dao).lsAllocateByBudgetYear(budgetYear));
				request.setAttribute("listVender", listVender);
				request.setAttribute("listProviceC", this.dataUtil(dao).lsProvince());
				request.setAttribute("listCategory", listCategory);
				request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
				request.setAttribute("expenseStatus", this.properties().statusExpense());
				request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
					
				request.setAttribute("job", "edit");
				request.setAttribute("listExpenseTransfer", listExpenseTransfer);
				request.setAttribute("count", maxSeq);
				request.setAttribute("maxSeq", maxSeq);
				request.setAttribute("docNo", expenseBean.getDocNo());
				request.setAttribute("expenseBean", expenseBean);
				request.setAttribute("display", "");
				request.setAttribute("pv", pv);
			}else{
				this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60301B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	

	private String ACC60302upload(HttpServletRequest request, MultipartFormDataRequest mrequest){
		Connection conn = null;
		ISUD dao = null;
		Hashtable files = mrequest.getFiles();
		UploadFile file = (UploadFile)files.get("xlsFile");
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			List expenseCategoryList = new ArrayList();
			List allocateList = new ArrayList();
			List expenseChequeList = new ArrayList();
			List list = new ArrayList();
			StringBuffer sb = new StringBuffer();
			
			int j =0;
			Map<String, String> map = new HashMap<String, String>();
			double remain = 0d;
			String nagative = "";
			String provinceId = mrequest.getParameter("provinceId");
			String budgetYear = mrequest.getParameter("budgetYear");
			String expenseDate = mrequest.getParameter("expenseDate");
			String expenseSubject = mrequest.getParameter("expenseSubject");
			String expenseDesc = mrequest.getParameter("expenseDesc");
			String docNo = this.genCode(dao).getExpenseDocNo(budgetYear,provinceId,"C");
			String[] fileName = null;
			Date date = new Date();
			Calendar currentDate = Calendar.getInstance();
			currentDate.setTime(date);
			Acc6ExpenseChequeBean expenseChequeBean = new Acc6ExpenseChequeBean();
			fileName = file.getFileName().split("\\.");
			int cv = 0;
			if(fileName[1].equals("xls")){
				POIFSFileSystem fsFileSystem = new POIFSFileSystem(file.getInpuStream());
				HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
				HSSFSheet hssfSheet = workBook.getSheetAt(0);
				int rowNum = hssfSheet.getLastRowNum();
				if(rowNum > 0){
					Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
					Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
					Acc6AllocateBean allocateBean = new Acc6AllocateBean();
					expenseCategoryBean.setCategoryAcc("6");
					expenseCategoryBean.setActive("Y");
					list = dao.selectList(expenseCategoryBean);
					for (j =0;j<list.size();j++){
						Acc67ExpenseCategoryBean bean = (Acc67ExpenseCategoryBean)list.get(j);
						expenseCategoryList.add(bean.getCategoryId());
					}
					//allocateBean.setBudgetYear(budgetYear);
					list = dao.selectList(allocateBean);
					for (j =0;j<list.size();j++){
						Acc6AllocateBean bean = (Acc6AllocateBean)list.get(j);
						allocateList.add(bean.getAllocateCode());
					}
					list = dao.selectList(expenseChequeBean, " AND DOC_NO LIKE 'ACC6"+budgetYear+"C"+provinceId+"%' AND ACTIVE = 'Y' ");
					for (j =0;j<list.size();j++){
						Acc6ExpenseChequeBean bean = (Acc6ExpenseChequeBean)list.get(j);
						expenseChequeList.add(bean.getChequeNo());
					}
					UserInfo userInfo = new UserInfo();
					expenseBean.setDocNo(docNo);
					expenseBean.setExpenseType("C");
					expenseBean.setBudgetYear(budgetYear);
					expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(this.encodeUTF(expenseDate)));
					expenseBean.setProvinceId(provinceId);
					expenseBean.setExpenseSubject(this.encodeUTF(expenseSubject));
					expenseBean.setExpenseDesc(this.encodeUTF(expenseDesc));
					expenseBean.setCreatedBy(userInfo.getUserId());
					expenseBean.setUpdatedBy(userInfo.getUserId());
					dao.insert(expenseBean);
					
					for(int i=0;i<rowNum;i++){
						expenseChequeBean = new Acc6ExpenseChequeBean();
						HSSFRow row = hssfSheet.getRow(i+1);	
						HSSFCell cellA1 = row.getCell(0);//เอกสารอ้างอิง	
						HSSFCell cellA2 = row.getCell(1);//รหัสแจ้งจัดสรรเงินให้คู่สัญญา
						HSSFCell cellA3 = row.getCell(2);//รหัสหน่วยบริการ/คู่สัญญา
						HSSFCell cellA4 = row.getCell(4);//เลขที่เช็ค
						HSSFCell cellA5 = row.getCell(5);//จำนวนเงิน
						HSSFCell cellA6 = row.getCell(6);//ภาษี
						HSSFCell cellA7 = row.getCell(7);//วันที่ออกเช็ค
						HSSFCell cellA8 = row.getCell(8);//วันที่จ่ายเช็ค
						HSSFCell cellA9 = row.getCell(9);//รหัสหมวดการจ่าย	
						HSSFCell cellA10 = row.getCell(11);//ชื่อผู้รับผิดชอบ
						HSSFCell cellA11 = row.getCell(12);//คำอธิบาย
						HSSFCell cellA12 = row.getCell(13);//สถานะเงินยืม
						
						if(cellA1 != null){
							cellA1.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA2 != null){
							cellA2.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA3 != null){
							cellA3.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA4 != null){
							cellA4.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA5 != null){
							cellA5.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA6 != null){
							cellA6.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA7 != null){
							cellA7.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA8 != null){
							cellA8.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA9 != null){
							cellA9.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA10 != null){
							cellA10.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA11 != null){
							cellA11.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellA12 != null){
							cellA12.setCellType(Cell.CELL_TYPE_STRING);
						}
						expenseChequeBean.setDocNo(docNo);
						expenseChequeBean.setChequeSeq(String.valueOf(i+1));
						if (!dao.chkData(cellA1).equals("") && dao.chkData(cellA1).length() <= 50) {
							expenseChequeBean.setRefNo(String.valueOf(dao.chkData(cellA1)));
						} else if (dao.chkData(cellA1).length() > 50){
							this.message(request).setMsg("E", "เลขที่เอกสารอ้างอิงบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 50 กรุณรตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (dao.chkData(cellA1).equals("")){
							this.message(request).setMsg("E", "เลขที่เอกสารอ้างอิงบรรทัดที่ : " + (i+2) +" ไม่พบเลขที่เอกสารอ้างอิงกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						}
						if (!dao.chkData(cellA2).equals("") && dao.chkData(cellA2).matches("\\d{1,8}") && allocateList.contains(dao.chkData(cellA2).trim())) {
							expenseChequeBean.setAllocateCode(String.valueOf(dao.chkData(cellA2)));
						} else if (!dao.chkData(cellA2).matches("\\d")){
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (dao.chkData(cellA2).length() > 8){
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 8 ตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (!allocateList.contains(dao.chkData(cellA2).trim())) {
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่มีรหัสนี้ในระบบกรุณาตรวจสอบกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (dao.chkData(cellA2).equals("")) {
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่พบรหัสแจ้งจัดสรรเงินให้คู่สัญญากรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						}
						cv = 0;
						sb.delete(0, sb.length());
						sb.append("SELECT COUNT(VENDER_CODE) AS COUNT_VENDER_CODE ");
						sb.append("FROM "+this.schema+".ACC6_VENDER ");
						sb.append("WHERE VENDER_CODE = '"+dao.chkData(cellA3)+"' AND VENDER_TYPE = 'C' ");
						cv = Integer.parseInt(dao.findDataSql(sb.toString()).get("COUNT_VENDER_CODE").toString());
						if (dao.chkData(cellA3).equals("")) {
							this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่พบรหัสคู่สัญญากรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else {
							if (dao.chkData(cellA3).length() <= 10 && cv > 0) {
								expenseChequeBean.setVenderCode(String.valueOf(dao.chkData(cellA3)));
							} else if (dao.chkData(cellA3).length() > 10) {
								this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 10 กรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (cv == 0) {
								this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" คู่สัญญานี้ไม่มีในระบบกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} 
						}
						if (!dao.chkData(cellA4).equals("") && dao.chkData(cellA4).length() <= 8 && !expenseChequeList.contains(dao.chkData(cellA4).trim())) {
							expenseChequeBean.setChequeNo(String.valueOf(dao.chkData(cellA4)));
						} else if (dao.chkData(cellA4).equals("")) {
							this.message(request).setMsg("E", "เลขที่ cheque บรรทัดที่ : " + (i+2) +" ไม่พบเลขที่ cheque กรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (dao.chkData(cellA4).length() > 8) {
							this.message(request).setMsg("E", "เลขที่ cheque บรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 8 กรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (expenseChequeList.contains(dao.chkData(cellA4).trim())) {
							this.message(request).setMsg("E", "เลขที่ cheque บรรทัดที่ : " + (i+2) +" เลขที่ cheque นี้มีแล้วตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (!dao.chkData(cellA4).matches("\\d")) {
							this.message(request).setMsg("E", "เลขที่ cheque บรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						}
						if (!dao.chkData(cellA5).equals("") && dao.chkData(cellA5).replace(",", "").matches("(\\.|\\d)+")) {
							expenseChequeBean.setChequeAmount(String.valueOf(dao.chkData(cellA5).replace(",", "")));
						} else if (!dao.chkData(cellA5).equals("")){
							this.message(request).setMsg("E", "จำนวนเงินบรรทัดที่ : " + (i+2) +" ไม่พบจำนวนเงินกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (!dao.chkData(cellA5).replace(",", "").matches("\\d")) {
							this.message(request).setMsg("E", "จำนวนเงินบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						}
						if (!dao.chkData(cellA6).equals("") && dao.chkData(cellA6).replace(",", "").matches("(\\.|\\d)+")) {
							expenseChequeBean.setTaxValue(String.valueOf(dao.chkData(cellA6).replace(",", "")));
						} else if (!dao.chkData(cellA6).equals("")){
							this.message(request).setMsg("E", "จำนวนภาษี : " + (i+2) +" ไม่พบจำนวนเงินกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if (!dao.chkData(cellA6).replace(",", "").matches("\\d")) {
							this.message(request).setMsg("E", "จำนวนภาษี : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						}
						if (!dao.chkData(cellA7).equals("") && (dao.chkData(cellA7).split("\\/").length == 3)) {
							expenseChequeBean.setChequeDate(this.dateUtil().changeDateToDB(String.valueOf(cellA7)));
						} else if (dao.chkData(cellA7).equals("")){
							this.message(request).setMsg("E", "วันที่ cheque บรรทัดที่ : " + (i+2) +" ไม่พบวันที่ cheque กรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} else if ((dao.chkData(cellA7).split("\\/").length != 3)){
							this.message(request).setMsg("E", "วันที่ cheque บรรทัดที่ : " + (i+2) +" ไม่ถูกรูปแบบ วัน/เดือน/ปี กรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						} 
						
						if (cellA8 != null && !String.valueOf(cellA8).equals("")){
							expenseChequeBean.setChequeReceivingDate(this.dateUtil().changeDateToDB(String.valueOf(cellA8)));
						}
						
						if (!dao.chkData(cellA9).equals("")) {
							if (dao.chkData(cellA9).matches("\\d+") && expenseCategoryList.contains(dao.chkData(cellA9).trim())) {
								expenseChequeBean.setCategoryId(String.valueOf(dao.chkData(cellA9)));
							} else if (dao.chkData(cellA9).equals("")) {
								this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" ไม่พบรหัสหมวดการจ่ายรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (!dao.chkData(cellA9).matches("\\d+")) {
								this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (!expenseCategoryList.contains(dao.chkData(cellA9).trim())) {
								this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" ไม่มีข้อมูลในระบบกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							}
						} else {
							expenseChequeBean.setCategoryId(dao.chkData(cellA9));
						}
						
						expenseChequeBean.setResponsibleName(String.valueOf(dao.chkData(cellA10)));
						expenseChequeBean.setChequeDesc(String.valueOf(dao.chkData(cellA11)));
						if (!dao.chkData(cellA12).equals("") && (dao.chkData(cellA12).equals("Y") || dao.chkData(cellA12).equals("N"))) {
							expenseChequeBean.setIsBorrow(String.valueOf(dao.chkData(cellA12)));
						} else if (dao.chkData(cellA12).equals("")) { 
							this.message(request).setMsg("E", "สถานะเงินยืมบรรทัดที่ : " + (i+2) +" ไม่พบสถานะเงินยืม");
							this.flag = false;
							conn.rollback();
							break;
						} else if ((!dao.chkData(cellA12).equals("Y") || !dao.chkData(cellA12).equals("N"))) {
							this.message(request).setMsg("E", "สถานะเงินยืมบรรทัดที่ : " + (i+2) +" ไม่ถูกต้อง");
							this.flag = false;
							conn.rollback();
							break;
						}
						expenseChequeBean.setCreatedBy(userInfo.getUserId());
						expenseChequeBean.setUpdatedBy(userInfo.getUserId());
						map = this.getRemain(provinceId, budgetYear, String.valueOf(dao.chkData(cellA2)));
						remain = Double.parseDouble(map.get("remain").toString());
						nagative = map.get("nagative").toString();
						allocateBean = new Acc6AllocateBean();
						allocateBean.setAllocateCode(dao.chkData(cellA2));
						allocateBean = (Acc6AllocateBean) dao.select(allocateBean);
						if (allocateBean.getAllocateApply().equals("Y")) {
							if (remain < Double.parseDouble(dao.chkData(cellA5).replaceAll(",", "")) && nagative.equals("0")){
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ยอดคงเหลือ "+this.digitUtil().formatDecimal(remain, 2)+" ติดลบไม่ได้ ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (remain < 0 && (nagative.equals("1") || nagative.equals("0"))){
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ยอดคงเหลือ "+this.digitUtil().formatDecimal(remain, 2)+" ติดลบได้ 1 ครั้ง");
								this.flag = false;
								conn.rollback();
								break;
							} else {
								dao.insert(expenseChequeBean);
							}
						} else {
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" อยู่ในสถานะไม่ใช้งานกรุณาตรวจสอบ");
							this.flag = false;
							conn.rollback();
							break;
						}
						expenseChequeList.add(dao.chkData(cellA4).toString().trim());
					}
				} else {
					this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
				}
			}else{
				this.message(request).setMsg("W", "ไฟล์ excel ต้องเป็นนามสกุล xls");
			}
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("docNo", docNo);
			
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				if (this.flag) {
					this.ACC60302viewEdit(request);
				} else {
					this.ACC60302add(request);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String ACC60302confirmUpload(HttpServletRequest request, MultipartFormDataRequest mrequest){
		Connection conn = null;
		ISUD dao = null;
		Hashtable files = mrequest.getFiles();
		UploadFile file = (UploadFile)files.get("xlsFile");
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			List expenseCategoryList = new ArrayList();
			List allocateList = new ArrayList();
			List expenseChequeList = new ArrayList();
			List list = new ArrayList();
			int cv = 0;
			StringBuffer sb = new StringBuffer();
			UserInfo userInfo = new UserInfo();
			Map<String, String> map = new HashMap<String, String>();
			double remain = 0d;
			String nagative = "";
			int j =0;
			String[] fileName = null;
			Date date = new Date();
			Calendar currentDate = Calendar.getInstance();
			currentDate.setTime(date);
			String docNo = mrequest.getParameter("docNo");
			String provinceId = mrequest.getParameter("provinceId");
			String budgetYear = mrequest.getParameter("budgetYear");
			List listExpenseCheque = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			Acc6AllocateBean allocateBean = new Acc6AllocateBean();
			Acc6ExpenseChequeBean expenseChequeBean = new Acc6ExpenseChequeBean();
			expenseCategoryBean.setCategoryAcc("6");
			expenseCategoryBean.setActive("Y");
			list = dao.selectList(expenseCategoryBean);
			for (j =0;j<list.size();j++){
				Acc67ExpenseCategoryBean bean = (Acc67ExpenseCategoryBean)list.get(j);
				expenseCategoryList.add(bean.getCategoryId());
			}
			//allocateBean.setBudgetYear(budgetYear);
			//allocateBean.setAllocateApply("Y");
			list = dao.selectList(allocateBean);
			for (j =0;j<list.size();j++){
				Acc6AllocateBean bean = (Acc6AllocateBean)list.get(j);
				allocateList.add(bean.getAllocateCode());
			}
			list = dao.selectList(expenseChequeBean, " AND DOC_NO LIKE 'ACC6"+budgetYear+"C"+provinceId+"%' AND ACTIVE = 'Y' ");
			for (j =0;j<list.size();j++){
				Acc6ExpenseChequeBean bean = (Acc6ExpenseChequeBean)list.get(j);
				expenseChequeList.add(bean.getChequeNo());
			}
			fileName = file.getFileName().split("\\.");
			docNo = this.encodeUTF(docNo);
			if(docNo.equals("สร้างจากระบบ")){
				docNo = this.genCode(dao).getExpenseDocNo(budgetYear,provinceId,"C");
				String expenseDate = mrequest.getParameter("expenseDate");
				String expenseSubject = mrequest.getParameter("expenseSubject");
				String expenseDesc = mrequest.getParameter("expenseDesc");
				
				if(fileName[1].equals("xls")){
					POIFSFileSystem fsFileSystem = new POIFSFileSystem(file.getInpuStream());
					HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
					HSSFSheet hssfSheet = workBook.getSheetAt(0);
					int rowNum = hssfSheet.getLastRowNum();
					if(rowNum > 0){
						expenseDate = this.encodeUTF(expenseDate);
						expenseSubject = this.encodeUTF(expenseSubject);
						expenseDesc = this.encodeUTF(expenseDesc);
						
						expenseBean = new Acc6ExpenseBean();
						expenseBean.setDocNo(docNo);
						expenseBean.setExpenseStatus("A");
						expenseBean.setExpenseType("C");
						expenseBean.setBudgetYear(budgetYear);
						expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
						expenseBean.setProvinceId(provinceId);
						expenseBean.setExpenseSubject(expenseSubject);
						expenseBean.setExpenseDesc(expenseDesc);
						expenseBean.setCreatedBy(this.user(request).getUserId());
						expenseBean.setUpdatedBy(this.user(request).getUserId());
						dao.insert(expenseBean);
						for(int i=0;i<rowNum;i++){
							expenseChequeBean = new Acc6ExpenseChequeBean();
							HSSFRow row = hssfSheet.getRow(i+1);	
							HSSFCell cellA1 = row.getCell(0);//เอกสารอ้างอิง	
							HSSFCell cellA2 = row.getCell(1);//รหัสแจ้งจัดสรรเงินให้คู่สัญญา
							HSSFCell cellA3 = row.getCell(2);//รหัสหน่วยบริการ/คู่สัญญา
							HSSFCell cellA4 = row.getCell(4);//เลขที่เช็ค
							HSSFCell cellA5 = row.getCell(5);//จำนวนเงิน
							HSSFCell cellA6 = row.getCell(6);//ภาษี
							HSSFCell cellA7 = row.getCell(7);//วันที่ออกเช็ค
							HSSFCell cellA8 = row.getCell(8);//วันที่จ่ายเช็ค
							HSSFCell cellA9 = row.getCell(9);//รหัสหมวดการจ่าย	
							HSSFCell cellA10 = row.getCell(11);//ชื่อผู้รับผิดชอบ
							HSSFCell cellA11 = row.getCell(12);//คำอธิบาย
							HSSFCell cellA12 = row.getCell(13);//สถานะเงินยืม
							if(cellA1 != null){
								cellA1.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA2 != null){
								cellA2.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA3 != null){
								cellA3.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA4 != null){
								cellA4.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA5 != null){
								cellA5.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA6 != null){
								cellA6.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA8 != null){
								cellA8.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA9 != null){
								cellA9.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA10 != null){
								cellA10.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA11 != null){
								cellA11.setCellType(Cell.CELL_TYPE_STRING);
							}
							if(cellA12 != null){
								cellA12.setCellType(Cell.CELL_TYPE_STRING);
							}
							
							expenseChequeBean.setDocNo(docNo);
							expenseChequeBean.setChequeSeq(String.valueOf(i+1));
							if (!dao.chkData(cellA1).equals("") && dao.chkData(cellA1).length() <= 50) {
								expenseChequeBean.setRefNo(String.valueOf(dao.chkData(cellA1)));
							} else if (dao.chkData(cellA1).length() > 50){
								this.message(request).setMsg("E", "เลขที่เอกสารอ้างอิงบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 50 กรุณรตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (dao.chkData(cellA1).equals("")){
								this.message(request).setMsg("E", "เลขที่เอกสารอ้างอิงบรรทัดที่ : " + (i+2) +" ไม่พบเลขที่เอกสารอ้างอิงกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							}
							if (!dao.chkData(cellA2).equals("") && dao.chkData(cellA2).matches("\\d{1,8}") && allocateList.contains(dao.chkData(cellA2).trim())) {
								expenseChequeBean.setAllocateCode(String.valueOf(dao.chkData(cellA2)));
							} else if (!dao.chkData(cellA2).matches("\\d")){
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (dao.chkData(cellA2).length() > 8){
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 8 ตัวอักษรกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (!allocateList.contains(dao.chkData(cellA2).trim())) {
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่มีรหัสนี้ในระบบกรุณาตรวจสอบกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (dao.chkData(cellA2).equals("")) {
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่พบรหัสแจ้งจัดสรรเงินให้คู่สัญญากรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							}
							cv = 0;
							sb.delete(0, sb.length());
							sb.append("SELECT COUNT(VENDER_CODE) AS COUNT_VENDER_CODE ");
							sb.append("FROM "+this.schema+".ACC6_VENDER ");
							sb.append("WHERE VENDER_CODE = '"+dao.chkData(cellA3)+"' AND VENDER_TYPE = 'C' ");
							cv = Integer.parseInt(dao.findDataSql(sb.toString()).get("COUNT_VENDER_CODE").toString());
							if (dao.chkData(cellA3).equals("")) {
								this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่พบรหัสคู่สัญญากรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else {
								if (dao.chkData(cellA3).length() <= 10 && cv > 0) {
									expenseChequeBean.setVenderCode(String.valueOf(dao.chkData(cellA3)));
								} else if (dao.chkData(cellA3).length() > 10) {
									this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 10 กรุณาตรวจสอบ");
									this.flag = false;
									conn.rollback();
									break;
								} else if (cv == 0) {
									this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" คู่สัญญานี้ไม่มีในระบบกรุณาตรวจสอบ");
									this.flag = false;
									conn.rollback();
									break;
								} 
							}
							if (!dao.chkData(cellA4).equals("") && dao.chkData(cellA4).length() <= 8 && !expenseChequeList.contains(dao.chkData(cellA4).trim())) {
								expenseChequeBean.setChequeNo(String.valueOf(dao.chkData(cellA4)));
							} else if (dao.chkData(cellA4).equals("")) {
								this.message(request).setMsg("E", "เลขที่ cheque บรรทัดที่ : " + (i+2) +" ไม่พบเลขที่ cheque กรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (dao.chkData(cellA4).length() > 8) {
								this.message(request).setMsg("E", "เลขที่ cheque บรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 8 กรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (expenseChequeList.contains(dao.chkData(cellA4).trim())) {
								this.message(request).setMsg("E", "เลขที่ cheque บรรทัดที่ : " + (i+2) +" เลขที่ cheque นี้มีแล้วตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (!dao.chkData(cellA4).matches("\\d")) {
								this.message(request).setMsg("E", "เลขที่ cheque บรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							}
							if (!dao.chkData(cellA5).equals("") && dao.chkData(cellA5).replace(",", "").matches("(\\.|\\d)+")) {
								expenseChequeBean.setChequeAmount(String.valueOf(dao.chkData(cellA5).replace(",", "")));
							} else if (!dao.chkData(cellA5).equals("")){
								this.message(request).setMsg("E", "จำนวนเงินบรรทัดที่ : " + (i+2) +" ไม่พบจำนวนเงินกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (!dao.chkData(cellA5).replace(",", "").matches("\\d")) {
								this.message(request).setMsg("E", "จำนวนเงินบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							}
							if (!dao.chkData(cellA6).equals("") && dao.chkData(cellA6).replace(",", "").matches("(\\.|\\d)+")) {
								expenseChequeBean.setTaxValue(String.valueOf(dao.chkData(cellA6).replace(",", "")));
							} else if (!dao.chkData(cellA6).equals("")){
								this.message(request).setMsg("E", "จำนวนภาษี : " + (i+2) +" ไม่พบจำนวนเงินกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if (!dao.chkData(cellA6).replace(",", "").matches("\\d")) {
								this.message(request).setMsg("E", "จำนวนภาษี : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							}

							if (!dao.chkData(cellA7).equals("") && (dao.chkData(cellA7).split("\\/").length == 3)) {
								expenseChequeBean.setChequeDate(this.dateUtil().changeDateToDB(String.valueOf(cellA7)));
							} else if (dao.chkData(cellA7).equals("")){
								this.message(request).setMsg("E", "วันที่ cheque บรรทัดที่ : " + (i+2) +" ไม่พบวันที่ cheque กรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							} else if ((dao.chkData(cellA7).split("\\/").length != 3)){
								this.message(request).setMsg("E", "วันที่ cheque บรรทัดที่ : " + (i+2) +" ไม่ถูกรูปแบบ วัน/เดือน/ปี กรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							}
							
							if (cellA8 != null && !String.valueOf(cellA8).equals("")){
								expenseChequeBean.setChequeReceivingDate(this.dateUtil().changeDateToDB(String.valueOf(cellA8)));
							}
							
							if (!dao.chkData(cellA9).equals("")) {
								if (dao.chkData(cellA9).matches("\\d+") && expenseCategoryList.contains(dao.chkData(cellA9).trim())) {
									expenseChequeBean.setCategoryId(String.valueOf(dao.chkData(cellA9)));
								} else if (dao.chkData(cellA9).equals("")) {
									this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" ไม่พบรหัสหมวดการจ่ายรุณาตรวจสอบ");
									this.flag = false;
									conn.rollback();
									break;
								} else if (!dao.chkData(cellA9).matches("\\d+")) {
									this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
									this.flag = false;
									conn.rollback();
									break;
								} else if (!expenseCategoryList.contains(dao.chkData(cellA9).trim())) {
									this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" ไม่มีข้อมูลในระบบกรุณาตรวจสอบ");
									this.flag = false;
									conn.rollback();
									break;
								}
							} else {
								expenseChequeBean.setCategoryId(dao.chkData(cellA9));
							}
							
							expenseChequeBean.setResponsibleName(String.valueOf(dao.chkData(cellA10)));
							expenseChequeBean.setChequeDesc(String.valueOf(dao.chkData(cellA11)));
							if (!dao.chkData(cellA12).equals("") && (dao.chkData(cellA12).equals("Y") || dao.chkData(cellA12).equals("N"))) {
								expenseChequeBean.setIsBorrow(String.valueOf(dao.chkData(cellA12)));
							} else if (dao.chkData(cellA12).equals("")) { 
								this.message(request).setMsg("E", "สถานะเงินยืมบรรทัดที่ : " + (i+2) +" ไม่พบสถานะเงินยืม");
								this.flag = false;
								conn.rollback();
								break;
							} else if ((!dao.chkData(cellA12).equals("Y") || !dao.chkData(cellA12).equals("N"))) {
								this.message(request).setMsg("E", "สถานะเงินยืมบรรทัดที่ : " + (i+2) +" ไม่ถูกต้อง");
								this.flag = false;
								conn.rollback();
								break;
							}
							expenseChequeBean.setCreatedBy(userInfo.getUserId());
							expenseChequeBean.setUpdatedBy(userInfo.getUserId());
							map = this.getRemain(provinceId, budgetYear, String.valueOf(dao.chkData(cellA2)));
							remain = Double.parseDouble(map.get("remain").toString());
							nagative = map.get("nagative").toString();
							allocateBean = new Acc6AllocateBean();
							allocateBean.setAllocateCode(dao.chkData(cellA2));
							allocateBean = (Acc6AllocateBean) dao.select(allocateBean);
							
							if (allocateBean.getAllocateApply().equals("Y")) {
								if (remain < Double.parseDouble(dao.chkData(cellA5).replaceAll(",", "")) && nagative.equals("0")){
									this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ยอดคงเหลือ "+this.digitUtil().formatDecimal(remain, 2)+" ติดลบไม่ได้ ");
									this.flag = false;
									conn.rollback();
									break;
								} else if (remain < 0 && (nagative.equals("1") || nagative.equals("0"))){
									this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ยอดคงเหลือ "+this.digitUtil().formatDecimal(remain, 2)+" ติดลบได้ 1 ครั้ง");
									this.flag = false;
									conn.rollback();
									break;
								} else {
									dao.insert(expenseChequeBean);
								}
							} else {
								this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" อยู่ในสถานะไม่ใช้งานกรุณาตรวจสอบ");
								this.flag = false;
								conn.rollback();
								break;
							}
							expenseChequeList.add(dao.chkData(cellA4).toString().trim());
						}

						expenseBean = new Acc6ExpenseBean();
						expenseBean.setDocNo(docNo);
						expenseBean.setProvinceId(provinceId);
						expenseBean.setBudgetYear(budgetYear);
						expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
						
						Map pv = new HashMap();
						expenseChequeBean = new Acc6ExpenseChequeBean();
						expenseChequeBean.setDocNo(docNo);
						expenseChequeBean.setActive("Y");
						listExpenseCheque = dao.selectList(expenseChequeBean," ORDER BY CHEQUE_SEQ ASC");
						for (int i = 0; i < listExpenseCheque.size(); i++) {
							Acc6ExpenseChequeBean bean = (Acc6ExpenseChequeBean) listExpenseCheque.get(i);
							sb.delete(0, sb.length());
							sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
							sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
							sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+bean.getAllocateCode()+"'");
							pv = dao.findDataSql(sb.toString());
							
							if (pv.get("ALLOCATE_NAME") == null){
								bean.setAllocateName("");
							}else{
								bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
							}
							if (pv.get("ALLOCATE_NAME_SUB") == null){
								bean.setAllocateSub("");
							}else{
								bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
							}
							
							sb.delete(0, sb.length());
							pv = new HashMap();
							sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
							sb.append("FROM "+this.schema+".ACC6_VENDER ");
							sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
							pv = dao.findDataSql(sb.toString());	
							
							if (pv.get("VENDER_NAME") == null){
								bean.setVenderName("");
							}else{
								bean.setVenderName(pv.get("VENDER_NAME").toString());
							}
							if (pv.get("VENDER_NAME_SUB") == null){
								bean.setVenderSub("");
							}else{
								bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
							}
						}
						request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
						request.setAttribute("docNo", docNo);
						request.setAttribute("expenseBean", expenseBean);
						request.setAttribute("listExpenseCheque", listExpenseCheque);
					} else if (rowNum < 0 && this.flag) {
						this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
					}
					
				}else{
					this.message(request).setMsg("W", "ไฟล์ excel ต้องเป็นนามสกุล xls");
				}
			}
		
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				if (this.flag) {
					this.ACC60302view(request);
				} else {
					this.ACC60302add(request);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String ACC60302viewEdit(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			List listCategory = new ArrayList();
			List listExpenseCheque = new ArrayList();
			List listVender = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseChequeBean expenseChequeBean = new Acc6ExpenseChequeBean();
			Acc6VenderBean venderBean = new Acc6VenderBean(); 

			if (!docNo.equals("") && !budgetYear.equals("") && !provinceId.equals("")){
				expenseCategoryBean.setCategoryAcc("6");
				expenseCategoryBean.setActive("Y");
				listCategory = dao.selectList(expenseCategoryBean);
	
				expenseBean.setDocNo(docNo);
				expenseBean.setBudgetYear(budgetYear);
				expenseBean.setProvinceId(provinceId);
				expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
						
				String sql = "";
				StringBuffer sb = new StringBuffer();
				Map pv = new HashMap();
				expenseChequeBean.setDocNo(docNo);
				listExpenseCheque = dao.selectList(expenseChequeBean," ORDER BY CHEQUE_SEQ ASC");
				for (int i = 0; i < listExpenseCheque.size(); i++) {
					Acc6ExpenseChequeBean bean = (Acc6ExpenseChequeBean) listExpenseCheque.get(i);
					String allocateCode = bean.getAllocateCode();
					String venderCode = bean.getVenderCode();
					sb.delete(0, sb.length());
					sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
					sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
					sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+allocateCode+"'");
					pv = dao.findDataSql(sb.toString());
					
					if (pv.get("ALLOCATE_NAME") == null){
						bean.setAllocateName("");
					}else{
						bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
					}
					if (pv.get("ALLOCATE_NAME_SUB") == null){
						bean.setAllocateSub("");
					}else{
						bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
					}
					
					sb.delete(0, sb.length());
					pv = new HashMap();
					sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
					sb.append("FROM "+this.schema+".ACC6_VENDER ");
					sb.append("WHERE VENDER_CODE='"+venderCode+"'");
					pv = dao.findDataSql(sb.toString());
					
					if (pv.get("VENDER_NAME") == null){
						bean.setVenderName("");
					}else{
						bean.setVenderName(pv.get("VENDER_NAME").toString());
					}
					if (pv.get("VENDER_NAME_SUB") == null){
						bean.setVenderSub("");
					}else{
						bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
					}
					
				}
					
				sql = "SELECT PROVINCE_ID, PROVINCE_NAME FROM " + this.schema + ".PROVINCE WHERE PROVINCE_ID != '"+this.user(request).getAreaCode()+"' AND NHSO_ZONE NOT IN ('14', '15') ORDER BY PROVINCE_NAME ASC";
				request.setAttribute("listAllProvice", dao.listDataSql(sql));
					
				String maxSeq = dao.chkData(dao.findDataSql("SELECT MAX(CHEQUE_SEQ) AS MAX_CHEQUE_SEQ FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE WHERE DOC_NO = '"+docNo+"'").get("MAX_CHEQUE_SEQ"));

				venderBean.setProvinceId(this.user(request).getAreaCode());
				listVender = dao.selectList(venderBean);
					
				request.setAttribute("lsAllocate", this.dataUtil(dao).lsAllocateByBudgetYear(budgetYear));
				request.setAttribute("listVender", listVender);
				request.setAttribute("listProviceC", this.dataUtil(dao).lsProvince());
				request.setAttribute("listCategory", listCategory);
				request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
				request.setAttribute("expenseStatus", this.properties().statusExpense());
				request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
					
				request.setAttribute("job", "edit");
				request.setAttribute("listExpenseCheque", listExpenseCheque);
				request.setAttribute("count", maxSeq);
				request.setAttribute("maxSeq", maxSeq);
				request.setAttribute("docNo", expenseBean.getDocNo());
				request.setAttribute("expenseBean", expenseBean);
				request.setAttribute("display", "");
				request.setAttribute("pv", pv);
			} else {
				this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
			}
			
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60302B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String ACC60301add(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			Acc6VenderBean venderBean = new Acc6VenderBean();
			List listCategory = null;
			List listVender = null;

			expenseCategoryBean.setCategoryAcc("6");
			expenseCategoryBean.setActive("Y");
			listCategory = dao.selectList(expenseCategoryBean);
			
			venderBean.setProvinceId(this.user(request).getAreaCode());
			listVender = dao.selectList(venderBean, " AND VENDER_ACCOUNT IS NOT NULL");
			
			request.setAttribute("listProviceC", this.dataUtil(dao).lsProvince());
			request.setAttribute("listCategory", listCategory);
			request.setAttribute("listVender", listVender);
			request.setAttribute("lsAllocate", this.dataUtil(dao).lsAllocateByBudgetYear(this.dateUtil().getBudgetYear()));
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("docNo", "สร้างจากระบบ");
			request.setAttribute("job", "save");
			request.setAttribute("count", "0");
			request.setAttribute("display", "none");
			request.setAttribute("now", this.dateUtil().getThaiCurrentDate());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60301B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String ACC60302add(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			Acc6VenderBean venderBean = new Acc6VenderBean();
			List listCategory = null;
			List listVender = null;

			expenseCategoryBean.setCategoryAcc("6");
			expenseCategoryBean.setActive("Y");
			listCategory = dao.selectList(expenseCategoryBean);
			
			venderBean.setProvinceId(this.user(request).getAreaCode());
			venderBean.setVenderType("C");
			listVender = dao.selectList(venderBean);
			
			request.setAttribute("listProviceC", this.dataUtil(dao).lsProvince());
			request.setAttribute("listCategory", listCategory);
			request.setAttribute("listVender", listVender);
			request.setAttribute("lsAllocate", this.dataUtil(dao).lsAllocateByBudgetYear(this.dateUtil().getBudgetYear()));
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("docNo", "สร้างจากระบบ");
			request.setAttribute("job", "save");
			request.setAttribute("count", "0");
			request.setAttribute("display", "none");
			request.setAttribute("now", this.dateUtil().getThaiCurrentDate());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60302B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String ACC60302view(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			List listExpenseCheque = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseChequeBean expenseChequeBean = new Acc6ExpenseChequeBean();
			
			expenseBean = new Acc6ExpenseBean();
			expenseBean.setDocNo(docNo);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
			
			StringBuffer sb = new StringBuffer();
			Map pv = new HashMap();
			expenseChequeBean.setDocNo(docNo);
			expenseChequeBean.setActive("Y");
			Map<String, String> xcols = new HashMap<String, String>();
			xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
			listExpenseCheque = dao.selectList(expenseChequeBean, xcols," ORDER BY CHEQUE_SEQ ASC");
			for (int i = 0; i < listExpenseCheque.size(); i++) {
				Acc6ExpenseChequeBean bean = (Acc6ExpenseChequeBean) listExpenseCheque.get(i);
				sb.delete(0, sb.length());
				sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
				sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
				sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+bean.getAllocateCode()+"'");
				pv = dao.findDataSql(sb.toString());
				if(pv.get("ALLOCATE_NAME") == null){
					bean.setAllocateName("");
				} else{
					bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
				}
				if(pv.get("ALLOCATE_NAME_SUB") == null){
					bean.setAllocateSub("");
				} else{
					bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
				}
				sb.delete(0, sb.length());
				pv = new HashMap();
				sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
				sb.append("FROM "+this.schema+".ACC6_VENDER ");
				sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
				pv = dao.findDataSql(sb.toString());
				if(pv.get("VENDER_NAME") == null){
					bean.setVenderName("");
				} else{
					bean.setVenderName(pv.get("VENDER_NAME").toString());
				}
				if(pv.get("VENDER_NAME_SUB") == null){
					bean.setVenderSub("");
				} else{
					bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
				}
				
			}
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("docNo", docNo);
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("listExpenseCheque", listExpenseCheque);
			request.setAttribute("now", this.dateUtil().getThaiCurrentDate());
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60302C.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String ACC60301view(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			List listExpenseTransfer = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			
			expenseBean = new Acc6ExpenseBean();
			expenseBean.setDocNo(docNo);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
			
			StringBuffer sb = new StringBuffer();
			Map pv = new HashMap();
			expenseTransferBean.setDocNo(docNo);
			expenseTransferBean.setActive("Y");
			Map<String, String> xcols = new HashMap<String, String>();
			xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
			listExpenseTransfer = dao.selectList(expenseTransferBean, xcols," ORDER BY TRANSFER_SEQ ASC");
			for (int i = 0; i < listExpenseTransfer.size(); i++) {
				Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpenseTransfer.get(i);
				sb.delete(0, sb.length());
				sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
				sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
				sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+bean.getAllocateCode()+"'");
				pv = dao.findDataSql(sb.toString());

				if (pv.get("ALLOCATE_NAME") == null){
					bean.setAllocateName("");
				}else{
					bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
				}
				if (pv.get("ALLOCATE_NAME_SUB") == null){
					bean.setAllocateSub("");
				}else{
					bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
				}
				
				sb.delete(0, sb.length());
				pv = new HashMap();
				sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
				sb.append("FROM "+this.schema+".ACC6_VENDER ");
				sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
				pv = dao.findDataSql(sb.toString());

				if (pv.get("VENDER_NAME") == null){
					bean.setVenderName("");
				}else{
					bean.setVenderName(pv.get("VENDER_NAME").toString());
				}
				if (pv.get("VENDER_NAME_SUB") == null){
					bean.setVenderSub("");
				}else{
					bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
				}
				
			}

			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("docNo", docNo);
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("listExpenseTransfer", listExpenseTransfer);
			request.setAttribute("now", this.dateUtil().getThaiCurrentDate());
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60301C.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private Map<String,String> getRemain(String provinceId, String budgetYear, String allocateCode){
		Connection conn = null;
		ISUD dao = null;
		Map<String,String> mapResult = new HashMap<String,String>();
		double remain = 0d;
		double sumAmount  = 0d;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String nagative = "";
			String sql = "";
			Map map = new HashMap();
			Map map2 = new HashMap();
			
			sql  = "SELECT NVL(NEGATIVE,'') AS NEGATIVE "+
			"FROM " + this.schema + ".ACC6_ALLOCATE "+
			"WHERE ALLOCATE_CODE = '"+allocateCode+"' ";
			map = dao.findDataSql(sql);
			nagative = map.get("NEGATIVE").toString();
			
			sql = "SELECT NVL(SUM(NVL(INCOME_AMOUNT,0))- SUM(NVL(EXPENSE_AMOUNT,0)),0) AS REMAIN "+
			"FROM ACC6_TRANSACTION "+
			"WHERE BUDGET_YEAR = '"+budgetYear+"' AND PROVINCE_ID = '"+provinceId+"' "+
			"AND ALLOCATE_CODE = '"+allocateCode+"' ";
			map = dao.findDataSql(sql);
			if (map.get("REMAIN") != null && !map.get("REMAIN").equals("")){
				remain = Double.parseDouble(map.get("REMAIN").toString());
			}
			sql = "SELECT NVL(SUM(AET.TRANSFER_AMOUNT),0) AS SUM_AMOUNT "+
			"FROM ACC6_EXPENSE AE, ACC6_EXPENSE_TRANSFER AET "+
			"WHERE AE.DOC_NO = AET.DOC_NO AND AET.ALLOCATE_CODE = '"+allocateCode+"' "+
			"AND AE.PROVINCE_ID = '"+provinceId+"' AND AE.BUDGET_YEAR = '"+budgetYear+"' AND AET.ACTIVE = 'Y' "+
			"AND AET.TRANSACTION_TIMESTAMP IS NULL ";
			map2 = dao.findDataSql(sql);
			sumAmount += Double.parseDouble(map2.get("SUM_AMOUNT").toString());
			
			sql = "SELECT NVL(SUM(AEC.CHEQUE_AMOUNT),0) AS SUM_AMOUNT "+
			"FROM ACC6_EXPENSE AE, ACC6_EXPENSE_CHEQUE AEC "+
			"WHERE AE.DOC_NO = AEC.DOC_NO AND AEC.ALLOCATE_CODE = '"+allocateCode+"' "+
			"AND AE.PROVINCE_ID = '"+provinceId+"' AND AE.BUDGET_YEAR = '"+budgetYear+"' AND AEC.ACTIVE = 'Y' "+
			"AND AEC.TRANSACTION_TIMESTAMP IS NULL ";
			map2 = dao.findDataSql(sql);
			sumAmount += Double.parseDouble(map2.get("SUM_AMOUNT").toString());
			remain = remain - sumAmount;
			mapResult.put("nagative", nagative);
			mapResult.put("remain", String.valueOf(remain));
		} catch (Exception e) {
			
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return mapResult;
	}
	
	private String ACC60107Upload(HttpServletRequest request, MultipartFormDataRequest mrequest){
		Connection conn = null;
		ISUD dao = null;
		Hashtable files = mrequest.getFiles();
		UploadFile file = (UploadFile)files.get("xlsFile");
		boolean isFinish = false;
		Acc6VenderBean avb = new Acc6VenderBean();
		String provinceId = "";
		boolean isBreak = false;
		try {
			UserInfo userInfo = this.user(request);
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			List lsVender = new ArrayList();
			provinceId = mrequest.getParameter("provinceId");
			
			String fileName = file.getFileName();
			
			if (!dao.chkData(provinceId).equals("")) {
				if(fileName.substring(fileName.length() - 3, fileName.length()).equals("xls")){
					POIFSFileSystem fsFileSystem = new POIFSFileSystem(file.getInpuStream());
					HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
					HSSFSheet hssfSheet = workBook.getSheetAt(0);
					int rowNum = hssfSheet.getLastRowNum();
					if(rowNum > 0){
						for(int i=1 ; i <= rowNum ; i++){
							avb = new Acc6VenderBean();
							lsVender = new ArrayList();
							HSSFRow row = hssfSheet.getRow(i);
							HSSFCell venderCode = row.getCell(0);
							HSSFCell vanderName = row.getCell(1);
							HSSFCell venderBankCode = row.getCell(2);
							HSSFCell venderBankBranchCode = row.getCell(4);
							HSSFCell venderBankBranchName= row.getCell(5);
							HSSFCell venderAccount = row.getCell(6);
							HSSFCell venderAccountNameTh = row.getCell(7);
							
							venderCode.setCellType(Cell.CELL_TYPE_STRING);
							if (venderAccount != null) {
								venderAccount.setCellType(Cell.CELL_TYPE_STRING);
							}
							if (venderBankCode != null) {
								venderBankCode.setCellType(Cell.CELL_TYPE_STRING);
							}
							
							avb.setProvinceId(provinceId);
							avb.setVenderCode(dao.chkData(venderCode));
							
							lsVender = dao.selectList(avb);
							avb.setVenderName(dao.chkData(vanderName));
							avb.setVenderBankCode(dao.chkData(venderBankCode));
							avb.setVenderBankBranchCode(dao.chkData(venderBankBranchCode));
							avb.setVenderBankBranchName(dao.chkData(venderBankBranchName));
							avb.setVenderAccount(dao.chkData(venderAccount));
							if (dao.chkData(venderAccount).equals("")) {
								avb.setVenderType("C");
							} else {
								avb.setVenderType("T");
							}
							avb.setVenderAccountNameTh(dao.chkData(venderAccountNameTh));
							avb.setActive("Y");
							avb.setUpdatedBy(userInfo.getUserId());
							avb.setUpdatedDate(this.sysDate);
							if (lsVender.size() > 0) {
								dao.update(avb);
							} else {
								Acc6VenderBean avb2 = new Acc6VenderBean();
								avb2.setVenderCode(dao.chkData(venderCode));
								if (dao.selectList(avb2).size() > 0) {
									this.message(request).setMsg("W", "รหัสคู่สัญญา : " + dao.chkData(venderCode) + "มีในระบบแล้วกรุณาระบุใหม่");
									isBreak = true;
									conn.rollback();
									break;
								} else {
									avb.setCreatedDate(this.sysDate);
									avb.setCreatedBy(userInfo.getUserId());
									dao.insert(avb);
								}
							}
							isFinish = true;
						}
						if (!isBreak) {
							conn.commit();
						}
					}else{
						this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
					}
				}else{
					this.message(request).setMsg("W", "ไฟล์ excel ต้องเป็นนามสกุล xls");
				}
			} else {
				this.message(request).setMsg("W", "กรุณาเลือกจังหวัด");
			}
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			dao.printMessage();
			this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				if (isFinish) {
					this.search(request);
				} else {
					request.setAttribute("provinceId", provinceId);
					this.url = "/apps/acc6/ACC60107D.jsp";
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String condition = "";
			
			Acc6VenderBean venderBean = new Acc6VenderBean();
			PageBean pageBean = new PageBean();
			String provinceId = this.getAttr(request, "provinceId");
			String vanderName = this.getAttr(request, "vanderName");
			String venderCode = this.getAttr(request, "venderCode");
			String[] vendetType = request.getParameterValues("vendetType");
			String[] strSplit = null;
			venderBean.setProvinceId(provinceId);
			venderBean.setVenderCode(venderCode);
			venderBean.setVenderName(vanderName);
			
			if(vendetType == null){
				condition = "";
			}else{
				condition += "AND (";
				for (int i = 0; i < vendetType.length; i++) {
					strSplit = vendetType[i].split("\\|");
					if(strSplit.length > 0){
						for(int j=0;j<strSplit.length;j++){
							condition += " VENDER_TYPE = '"+strSplit[j]+"' OR";
						}
					}else{
						condition += " VENDER_TYPE = '"+vendetType[i]+"' OR";
					}
					
					if(vendetType[i].equals("M|S")){
						request.setAttribute("vendetTypeM", vendetType[i]);
					}else if(vendetType[i].equals("A")){
						request.setAttribute("vendetTypeA", vendetType[i]);
					}else if(vendetType[i].equals("C|T")){
						request.setAttribute("vendetTypeP", vendetType[i]);
					}
				}
				condition = condition.substring(0, condition.length()-2);
				condition += ")";
			}

			if (this.user(request).getAreaLevel().equals("2")){
				condition = " AND PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '" + this.user(request).getAreaCode()+ "' ) ";
			}
			
			this.beanUtil().copyRequest2Bean(request, pageBean);
			pageBean = dao.selectPage(venderBean, condition, pageBean);
			
			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}
			request.setAttribute("pageBean", pageBean);
			request.setAttribute("venderCode", venderCode);
			request.setAttribute("vanderName", vanderName);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("job", "search");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60107A.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String ACC60310upload(HttpServletRequest request, MultipartFormDataRequest mrequest, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		Hashtable files = mrequest.getFiles();
		UploadFile file = (UploadFile)files.get("xlsFile");
		Acc6ChequeBean acb = new Acc6ChequeBean();
		Acc6ChequeDetailBean acdb = new Acc6ChequeDetailBean();
		String isConfirm = "";
		String provinceId = "";
		String docNo = "";
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			List expenseCategoryList = new ArrayList();
			List allocateList = new ArrayList();
			List venderList = new ArrayList();
			List list = new ArrayList();
			double sumChequeAmount = 0d;
			Map<String, String> map = new HashMap<String, String>();
			double remain = 0d;
			String nagative = "";
			int j =0;
			provinceId = mrequest.getParameter("provinceId");
			String chequeDate = mrequest.getParameter("chequeDate");
			String responsibleName = this.encodeUTF(mrequest.getParameter("responsibleName"));
			String chequeNo = mrequest.getParameter("chequeNo");
			String chequeDesc = this.encodeUTF(mrequest.getParameter("chequeDesc"));
			isConfirm = mrequest.getParameter("isConfirm");
			String yyyy = chequeDate.split("\\/")[2];
			docNo = this.genCode(dao).getCuequeDocNo(yyyy, provinceId, "C");
			UserInfo ui = this.user(request);
			String chequeStatus = "";
			
			String[] fileName = null;
			fileName = file.getFileName().split("\\.");
			
			if(fileName[1].equals("xls")){
				POIFSFileSystem fsFileSystem = new POIFSFileSystem(file.getInpuStream());
				HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
				HSSFSheet hssfSheet = workBook.getSheetAt(0);
				int rowNum = hssfSheet.getLastRowNum();
				
				Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
				Acc6AllocateBean allocateBean = new Acc6AllocateBean();
				Acc6VenderBean avb = new Acc6VenderBean();
				expenseCategoryBean.setCategoryAcc("6");
				expenseCategoryBean.setActive("Y");
				list = dao.selectList(expenseCategoryBean);
				for (j =0;j<list.size();j++){
					Acc67ExpenseCategoryBean bean = (Acc67ExpenseCategoryBean)list.get(j);
					expenseCategoryList.add(bean.getCategoryId());
				}
				allocateBean.setActive("Y");
				list = dao.selectList(allocateBean);
				for (j =0;j<list.size();j++){
					Acc6AllocateBean bean = (Acc6AllocateBean)list.get(j);
					allocateList.add(bean.getAllocateCode());
				}
				avb.setActive("Y");
				avb.setVenderType("C");
				list = dao.selectList(avb);
				for (j =0;j<list.size();j++){
					Acc6VenderBean bean = (Acc6VenderBean)list.get(j);
					venderList.add(bean.getVenderCode());
				}
				
				if (isConfirm.equals("N")) {
					chequeStatus = "P";
				} else {
					chequeStatus = "A";
				}
				
				acb.setDocNo(docNo);
				acb.setProvinceId(provinceId);
				acb.setChequeDate(this.dateUtil().changeDateToDB(chequeDate));
				acb.setResponsibleName(responsibleName);
				acb.setChequeNo(chequeNo);
				acb.setChequeDesc(chequeDesc);
				acb.setChequeStatus(chequeStatus);
				acb.setCreatedBy(ui.getUserId());
				acb.setUpdatedBy(ui.getUserId());
				
				if(rowNum > 0){
					for(int i=0;i<rowNum;i++){
						acdb = new Acc6ChequeDetailBean();
						HSSFRow row = hssfSheet.getRow(i+1);
						HSSFCell refNo = row.getCell(0);
						HSSFCell budgetYear = row.getCell(1);
						HSSFCell allocateCode = row.getCell(2);
						HSSFCell venderCode = row.getCell(4);
						HSSFCell categoryId = row.getCell(6);
						HSSFCell chequeAmount = row.getCell(8);
						HSSFCell tax = row.getCell(9);
						HSSFCell isBorrow = row.getCell(10);
						
						if(refNo != null){
							refNo.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(budgetYear != null){
							budgetYear.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(allocateCode != null){
							allocateCode.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(venderCode != null){
							venderCode.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(categoryId != null){
							categoryId.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(chequeAmount != null){
							chequeAmount.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(tax != null){
							tax.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(isBorrow != null){
							isBorrow.setCellType(Cell.CELL_TYPE_STRING);
						}
						
						
						acdb.setDocNo(docNo);
						acdb.setChequeSeq(String.valueOf(i+1));
						if (!dao.chkData(refNo).equals("") && dao.chkData(refNo).length() <= 50) {
							acdb.setRefNo(String.valueOf(dao.chkData(refNo)));
						} else if (dao.chkData(refNo).length() > 50){
							this.message(request).setMsg("E", "เลขที่เอกสารอ้างอิงบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 50 กรุณรตรวจสอบ");
							this.flag = false;
						} else if (dao.chkData(refNo).equals("")){
							this.message(request).setMsg("E", "เลขที่เอกสารอ้างอิงบรรทัดที่ : " + (i+2) +" ไม่พบเลขที่เอกสารอ้างอิงกรุณาตรวจสอบ");
							this.flag = false;
						}
						if (!dao.chkData(budgetYear).equals("") && dao.chkData(budgetYear).length() <= 4) {
							acdb.setBudgetYear(dao.chkData(budgetYear.toString()));
						} else if (dao.chkData(budgetYear).length() > 4){
							this.message(request).setMsg("E", "ปีงบประมาณบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 4 กรุณรตรวจสอบ");
							this.flag = false;
						} else if (dao.chkData(budgetYear).equals("")){
							this.message(request).setMsg("E", "ปีงบประมาณบรรทัดที่ : " + (i+2) +" ไม่มีข้อมูลกรุณาตรวจสอบ");
							this.flag = false;
						}
						
						if (!dao.chkData(allocateCode).equals("") && dao.chkData(allocateCode).matches("\\d{1,8}") && allocateList.contains(dao.chkData(allocateCode).trim())) {
							acdb.setAllocateCode(String.valueOf(dao.chkData(allocateCode)));
						} else if (!dao.chkData(allocateCode).matches("\\d")){
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
						} else if (dao.chkData(allocateCode).length() > 8){
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 8 ตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
						} else if (!allocateList.contains(dao.chkData(allocateCode).trim())) {
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่มีรหัสนี้ในระบบกรุณาตรวจสอบกรุณาตรวจสอบ");
							this.flag = false;
						} else if (dao.chkData(allocateCode).equals("")) {
							this.message(request).setMsg("E", "รหัสแจ้งจัดสรรเงินให้คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่มีข้อมูลกรุณาตรวจสอบ");
							this.flag = false;
						}
						
						if (dao.chkData(venderCode).equals("")) {
							this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" ไม่มีข้อมูลกรุณาตรวจสอบ");
							this.flag = false;
						} else {
							if (dao.chkData(venderCode).length() <= 10 && venderList.contains(dao.chkData(venderCode).trim())) {
								acdb.setVenderCode(String.valueOf(dao.chkData(venderCode)));
							} else if (dao.chkData(venderCode).length() > 10) {
								this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" ความยาวอักษรเกิน 10 กรุณาตรวจสอบ");
								this.flag = false;
							} else if (!venderList.contains(dao.chkData(venderCode).trim())) {
								this.message(request).setMsg("E", "รหัสหน่วยบริการ/คู่สัญญาบรรทัดที่ : " + (i+2) +" คู่สัญญานี้ไม่มีในระบบกรุณาตรวจสอบ");
								this.flag = false;
							} 
						}
						
						if (!dao.chkData(categoryId).equals("") && dao.chkData(categoryId).matches("\\d+") && expenseCategoryList.contains(dao.chkData(categoryId).trim())) {
							acdb.setCategoryId(String.valueOf(dao.chkData(categoryId)));
						} else if (dao.chkData(categoryId).equals("")) {
							this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" ไม่มีข้อมูลกรุณาตรวจสอบ");
							this.flag = false;
						} else if (!dao.chkData(categoryId).matches("\\d+")) {
							this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
						} else if (!expenseCategoryList.contains(dao.chkData(categoryId).trim())) {
							this.message(request).setMsg("E", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) +" ไม่มีข้อมูลในระบบกรุณาตรวจสอบ");
							this.flag = false;
						}
						if (!dao.chkData(chequeAmount).equals("") && dao.chkData(chequeAmount).replace(",", "").matches("(\\.|\\d)+")) {
							map = this.getRemain(provinceId, dao.chkData(budgetYear), dao.chkData(allocateCode));
							remain = Double.parseDouble(map.get("remain").toString());
							nagative = map.get("nagative").toString();
							if (remain < Double.parseDouble(dao.chkData(chequeAmount)) && nagative.equals("0")){
								this.message(request).setMsg("E", "จำนวนเงินในบรรทัดที่ : " + (i+2) +" ยอดคงเหลือ "+this.digitUtil().formatDecimal(remain, 2)+" ติดลบไม่ได้ ");
								this.flag = false;
							} else if (remain < 0 && (nagative.equals("1") || nagative.equals("0"))){
								this.message(request).setMsg("E", "จำนวนเงินในบรรทัดที่ : " + (i+2) +" ยอดคงเหลือ "+this.digitUtil().formatDecimal(remain, 2)+" ติดลบได้ 1 ครั้ง");
								this.flag = false;
							} else {
								acdb.setChequeAmount(String.valueOf(dao.chkData(chequeAmount).replace(",", "")));
								sumChequeAmount += Double.parseDouble(dao.chkData(chequeAmount).replace(",", ""));
							}
						} else if (dao.chkData(chequeAmount).equals("")){
							this.message(request).setMsg("E", "จำนวนเงินบรรทัดที่ : " + (i+2) +" ไม่มีข้อมูลกรุณาตรวจสอบ");
							this.flag = false;
						} else if (!dao.chkData(chequeAmount).replace(",", "").matches("\\d")) {
							this.message(request).setMsg("E", "จำนวนเงินบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
						}
						
						if (!dao.chkData(tax).equals("") && dao.chkData(tax).replace(",", "").matches("(\\.|\\d)+")) {
							acdb.setTaxValue(String.valueOf(dao.chkData(tax).replace(",", "")));
						} else if (!dao.chkData(tax).equals("")){
							this.message(request).setMsg("E", "จำนวนภาษีบรรทัดที่ : " + (i+2) +" ไม่มีข้อมูลกรุณาตรวจสอบ");
							this.flag = false;
						} else if (!dao.chkData(tax).replace(",", "").matches("\\d")) {
							this.message(request).setMsg("E", "จำนวนภาษีบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
							this.flag = false;
						}
						
						if (!dao.chkData(isBorrow).equals("") && (dao.chkData(isBorrow).equals("Y") || dao.chkData(isBorrow).equals("N"))) {
							acdb.setIsBorrow(String.valueOf(dao.chkData(isBorrow)));
						} else if (dao.chkData(isBorrow).equals("")) { 
							this.message(request).setMsg("E", "สถานะเงินยืมบรรทัดที่ : " + (i+2) +" ไม่พบสถานะเงินยืม");
							this.flag = false;
						} else if ((!dao.chkData(isBorrow).equals("Y") || !dao.chkData(isBorrow).equals("N"))) {
							this.message(request).setMsg("E", "สถานะเงินยืมบรรทัดที่ : " + (i+2) +" ไม่ถูกต้อง");
							this.flag = false;
						}

						acdb.setCreatedBy(ui.getUserId());
						acdb.setUpdatedBy(ui.getUserId());
						if (this.flag) {
							dao.insert(acdb);
						} else {
							conn.rollback();
							break;
						}
					}//end for
					acb.setSumChequeAmount(String.valueOf(sumChequeAmount));
					if (this.flag) {
						dao.insert(acb);
					}
				} else if (rowNum < 0) {
					this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
				}
			}else{
				this.message(request).setMsg("W", "ไฟล์ excel ต้องเป็นนามสกุล xls");
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				if (isConfirm.equals("N")) {
					request.setAttribute("flag", "");
				} else {
					request.setAttribute("flag", "Y");
				}
				//dao.printMessage();
				request.setAttribute("docNo", docNo);
				request.setAttribute("provinceId", provinceId);
				this.Acc60310edit(request);
				dao = null;
			} catch (SQLException e) {
				this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String Acc60310edit(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		String flag = this.getAttr(request, "flag");
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc6ChequeBean acb = new Acc6ChequeBean();
			Acc6ChequeDetailBean acdb = new Acc6ChequeDetailBean();
			List lsChequeDetail = new ArrayList();
			StringBuffer sb = new StringBuffer();
			Map pv = new HashMap();
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			acb.setDocNo(docNo);
			acb.setProvinceId(provinceId);
			dao.select(acb);
			acdb.setDocNo(docNo);
			acdb.setActive("Y");
			lsChequeDetail = dao.selectList(acdb);
			Map<String, String> xcols = new HashMap<String, String>();
			xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
			lsChequeDetail = dao.selectList(acdb, xcols," ORDER BY CHEQUE_SEQ ASC");
			for (int i = 0; i < lsChequeDetail.size(); i++) {
				Acc6ChequeDetailBean bean = (Acc6ChequeDetailBean) lsChequeDetail.get(i);
				sb.delete(0, sb.length());
				sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
				sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
				sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+bean.getAllocateCode()+"'");
				pv = dao.findDataSql(sb.toString());
				if(pv.get("ALLOCATE_NAME") == null){
					bean.setAllocateName("");
				} else{
					bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
				}
				if(pv.get("ALLOCATE_NAME_SUB") == null){
					bean.setAllocateSub("");
				} else{
					bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
				}
				sb.delete(0, sb.length());
				pv = new HashMap();
				sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME ");
				sb.append("FROM "+this.schema+".ACC6_VENDER ");
				sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
				pv = dao.findDataSql(sb.toString());
				if(pv.get("VENDER_NAME") == null){
					bean.setVenderName("");
				} else{
					bean.setVenderName(pv.get("VENDER_NAME").toString());
				}
				if(pv.get("VENDER_NAME_SUB") == null){
					bean.setVenderSub("");
				} else{
					bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
				}
				
			}
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			expenseCategoryBean.setCategoryAcc("6");
			expenseCategoryBean.setActive("Y");
			List listCategory = new ArrayList();
			listCategory = dao.selectList(expenseCategoryBean);
			
			request.setAttribute("listCategory", listCategory);
			request.setAttribute("listProviceC", this.dataUtil(dao).lsProvince());
			request.setAttribute("acb", acb);
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("lsChequeDetail", lsChequeDetail);
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("expenseStatus", this.properties().statusExpense());
			request.setAttribute("job", "update");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				if (flag == null || flag.equals("")) {
					this.url = "/apps/acc6/ACC60310B.jsp";
				} else {
					this.url = "/apps/acc6/ACC60310C.jsp";
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String ACC70302upload(HttpServletRequest request, MultipartFormDataRequest mrequest, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		Hashtable files = mrequest.getFiles();
		UploadFile file = (UploadFile)files.get("xlsFile");
		boolean isBreak = false;
		String docNo = "";
		String provinceId = mrequest.getParameter("provinceId");
		String budgetYear = mrequest.getParameter("budgetYear");
		String job = mrequest.getParameter("job");
		try {
			UserInfo userInfo = this.user(request);
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			provinceId = mrequest.getParameter("provinceId");
			budgetYear = mrequest.getParameter("budgetYear");
			String refNo = mrequest.getParameter("refNo");
			String expenseDate = mrequest.getParameter("expenseDate");
			String expenseDesc = mrequest.getParameter("expenseDesc");
			docNo = this.genCode(dao).getExpenseDocNoAcc7(budgetYear, provinceId, "E");
			String fileName = file.getFileName();
			List lsExpenseCategory = new ArrayList();

			Acc7ExpenseBean aeb = new Acc7ExpenseBean();
			Acc7ExpenseDetailBean edb = new Acc7ExpenseDetailBean();
			Acc67ExpenseCategoryBean ecb = new Acc67ExpenseCategoryBean();
			Acc7TransactionBean atb = new Acc7TransactionBean();
			String ts = "";
			
			ecb.setCategoryAcc("7");
			List lsData = dao.selectList(ecb);
			for (int i = 0 ; i < lsData.size() ; i++) {
				Acc67ExpenseCategoryBean bean = (Acc67ExpenseCategoryBean) lsData.get(i);
				lsExpenseCategory.add(bean.getCategoryId());
			}
			
			if(fileName.substring(fileName.length() - 3, fileName.length()).equals("xls")){
				POIFSFileSystem fsFileSystem = new POIFSFileSystem(file.getInpuStream());
				HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
				HSSFSheet hssfSheet = workBook.getSheetAt(0);
				int rowNum = hssfSheet.getLastRowNum();
				if(rowNum > 0){
					aeb.setDocNo(docNo);
					aeb.setProvinceId(provinceId);
					aeb.setBudgetYear(budgetYear);
					aeb.setRefNo(refNo);
					aeb.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
					aeb.setExpenseDesc(expenseDesc);
					aeb.setCreatedBy(userInfo.getUserId());
					aeb.setCreatedDate(this.sysDate);
					aeb.setUpdatedBy(userInfo.getUserId());
					aeb.setUpdatedDate(this.sysDate);
					for(int i=1 ; i <= rowNum ; i++){
						
						edb = new Acc7ExpenseDetailBean();
						HSSFRow row = hssfSheet.getRow(i);
						HSSFCell cellAmount = row.getCell(0);
						HSSFCell cellCategoryId = row.getCell(1);
						HSSFCell cellDesc = row.getCell(2);
						
						if(cellAmount != null){
							cellAmount.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellCategoryId != null){
							cellCategoryId.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellDesc != null){
							cellDesc.setCellType(Cell.CELL_TYPE_STRING);
						}

						edb.setDocNo(docNo);
						edb.setSeq(String.valueOf(i));
						
						if (!dao.chkData(cellAmount).equals("") && dao.chkData(cellAmount).replace(",", "").matches("(\\.|\\d)+")) {
							edb.setExpenseAmount(dao.chkData(cellAmount).replaceAll(",", ""));
						} else if (!dao.chkData(cellAmount).replace(",", "").matches("\\d")) {
							this.message(request).setMsg("W", "จำนวนเงินบรรทัดที่ : " + (i+2) +" มีตัวอักษรกรุณาตรวจสอบ");
							isBreak = true;
						} else if (dao.chkData(cellAmount).equals("")){
							this.message(request).setMsg("W", "จำนวนเงินบรรทัดที่ : " + (i+2) +" ไม่มีจำนวนเงินกรุณาตรวจสอบ ");
							isBreak = true;
						}
						
						if (lsExpenseCategory.contains(dao.chkData(cellCategoryId)) && !dao.chkData(cellCategoryId).equals("")) {
							edb.setCategoryId(dao.chkData(cellCategoryId));
						} else if (!lsExpenseCategory.contains(dao.chkData(cellCategoryId))) {
							this.message(request).setMsg("W", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) + " ไม่มีระบบกรุณาระบุใหม่");
							isBreak = true;
						} else if (dao.chkData(cellCategoryId).equals("")) {
							this.message(request).setMsg("W", "รหัสหมวดการจ่ายบรรทัดที่ : " + (i+2) + " ไม่มีรหัสหมวดการจ่ายกรุณาตรวจสอบ");
							isBreak = true;
						}
						
						if (!dao.chkData(cellDesc).equals("")) {
							edb.setExpenseDetailDesc(dao.chkData(cellDesc));
						} else {
							this.message(request).setMsg("W", "คำอธิบายรายการบรรทัดที่ : " + (i+2) + " ไม่มีคำอธิบายรายการกรุณาตรวจสอบ");
							isBreak = true;
						}
						
						if (job.equals("confirm")) {
							atb = new Acc7TransactionBean();
							ts = this.dateUtil().getCurrentTimeStamp2DB();
							edb.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
							
							atb.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
							atb.setTransactionType("E");
							atb.setBudgetYear(budgetYear);
							atb.setProvinceId(provinceId);
							atb.setRefNo(refNo);
							atb.setExpenseAmount(dao.chkData(cellAmount).replaceAll(",", ""));
							atb.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
							atb.setReasonCode("00");
							atb.setCategoryId(dao.chkData(cellCategoryId));
						}
						
						edb.setCreatedBy(userInfo.getUserId());
						edb.setCreatedDate(this.sysDate);
						edb.setUpdatedBy(userInfo.getUserId());
						edb.setUpdatedDate(this.sysDate);
						
						if (isBreak) {
							conn.rollback();
							break;
						} else {
							dao.insert(edb);
							if (job.equals("confirm")) {
								dao.insert(atb);
							}
						}
					}
					
					if (isBreak) {
						conn.rollback();
					} else {
						if (job.equals("confirm")) {
							aeb.setExpenseStatus("Y");
						}
						dao.insert(aeb);
					}
				}else{
					this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
				}
			}else{
				this.message(request).setMsg("W", "ไฟล์ excel ต้องเป็นนามสกุล xls");
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			dao.printMessage();
			this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				request.setAttribute("docNo", docNo);
				request.setAttribute("provinceId", provinceId);
				request.setAttribute("budgetYear", budgetYear);
				this.acc70302viewEdit(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String acc70302viewEdit(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			
			Acc7ExpenseBean expenseBean = new Acc7ExpenseBean();
			List listExpenseDetailBean = new ArrayList();
			Acc7ExpenseDetailBean expenseDetailBean = new Acc7ExpenseDetailBean();
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			List expenseCategoryList = new ArrayList();
			expenseBean.setDocNo(docNo);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean.setProvinceId(provinceId);
			expenseBean = (Acc7ExpenseBean)dao.select(expenseBean);
			expenseDetailBean.setDocNo(docNo);
			listExpenseDetailBean = dao.selectList(expenseDetailBean);
			expenseCategoryBean.setCategoryAcc("7");
			expenseCategoryBean.setCategoryApply("Y");
			expenseCategoryList = dao.selectList(expenseCategoryBean);
			
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("listExpenseDetailBean", listExpenseDetailBean);
			request.setAttribute("expenseCategoryList", expenseCategoryList);
			request.setAttribute("job", "edit");
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc7/ACC70302B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String acc60108Upload (HttpServletRequest request, MultipartFormDataRequest mrequest, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		Hashtable files = mrequest.getFiles();
		UploadFile file = (UploadFile)files.get("xlsFile");
		String suspendDate = mrequest.getParameter("suspendDate");
		ACC60108 acc60108 = new ACC60108(request, response);
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			List lsData = new ArrayList();
			String fileName = file.getFileName();
			String[] strArr = suspendDate.split("/");
			String mm = strArr[1];
			String yyyy = strArr[2];
			String suspendAmount = "";
			String baacAmount = "";
			String acc67Amount = "";
			String mode = "insert";
			Acc67SuspendBean asb = new Acc67SuspendBean();
			asb.setMm(mm);
			asb.setYyyy(yyyy);
			lsData = dao.selectList(asb);
			if (lsData.size() > 0) {
				mode = "update";
			}
			
			if(fileName.substring(fileName.length() - 3, fileName.length()).equals("xls")){
				POIFSFileSystem fsFileSystem = new POIFSFileSystem(file.getInpuStream());
				HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
				HSSFSheet hssfSheet = workBook.getSheetAt(0);
				int rowNum = hssfSheet.getLastRowNum();
				if(rowNum > 0){
					for(int i=1 ; i <= rowNum ; i++){
						
						HSSFRow row = hssfSheet.getRow(i);
						HSSFCell cellProvinceId = row.getCell(0);
						HSSFCell cellAcc67Amount = row.getCell(2);
						HSSFCell cellBaacAmount = row.getCell(3);
						
						if(cellProvinceId != null){
							cellProvinceId.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellAcc67Amount != null){
							cellAcc67Amount.setCellType(Cell.CELL_TYPE_STRING);
						}
						if(cellBaacAmount != null){
							cellBaacAmount.setCellType(Cell.CELL_TYPE_STRING);
						}

						baacAmount = dao.chkData(cellBaacAmount).replaceAll(",", "");
						if (baacAmount.equals("")) {
							baacAmount = "0";
						}
						acc67Amount = dao.chkData(cellAcc67Amount).replaceAll(",", "");
						if (acc67Amount.equals("")) {
							acc67Amount = "0";
						}
						suspendAmount = String.valueOf(Double.parseDouble(baacAmount) - Double.parseDouble(acc67Amount));
						
						asb = new Acc67SuspendBean();
						asb.setProvinceId(dao.chkData(cellProvinceId));
						asb.setMm(mm);
						asb.setYyyy(yyyy);
						asb.setAcc67Amount(acc67Amount);
						asb.setBaacAmount(baacAmount);
						asb.setSuspendAmount(suspendAmount);
						if (mode.equals("insert")) {
							dao.insert(asb);
						} else {
							dao.update(asb);
						}
						dao.printMessage();
					}
				}else{
					this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
				}
			}else{
				this.message(request).setMsg("W", "ไฟล์ excel ต้องเป็นนามสกุล xls");
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("W", "ไม่สามารถ Upload ข้อมูลได้");
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				this.url = "/apps/acc6/ACC60108A.jsp";
				request.setAttribute("suspendDate", suspendDate);
				acc60108.searchByDate(request);
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
}
